• Tùy chọn
• Đăng nhập
• Đăng ký
• Xem như trên Máy tính
• Accelerated Mobile Pages Theme

Tính giá trị 1 công thức lưu trong table

• Trang chủ

  • Thủ Thuật Access
  • Access Nâng Cao
  • Thư viện thủ thuật
  • Tính giá trị 1 công thức lưu trong table
• Tính giá trị 1 công thức lưu trong table

• Tính giá trị 1 công thức lưu trong table

  Noname > 30-08-10, 10:10 AM

  Hỏi: Mình có table tblData thế này
  
  tblData
  <table bgcolor="#ffffff" border="1" cellspacing="0"><span style="color: black; font-family: Arial;"></span><caption><b>tblData</b></caption> <thead>
  <tr> <th bgcolor="#c0c0c0" bordercolor="#000000"><span style="color: black; font-family: Arial; font-size: 10pt;">TK</span></th> <th bgcolor="#c0c0c0" bordercolor="#000000"><span style="color: black; font-family: Arial; font-size: 10pt;">NODAU</span></th> <th bgcolor="#c0c0c0" bordercolor="#000000"><span style="color: black; font-family: Arial; font-size: 10pt;">CODAU</span></th> <th bgcolor="#c0c0c0" bordercolor="#000000"><span style="color: black; font-family: Arial; font-size: 10pt;">PSNO</span></th> <th bgcolor="#c0c0c0" bordercolor="#000000"><span style="color: black; font-family: Arial; font-size: 10pt;">PSCO</span></th> <th bgcolor="#c0c0c0" bordercolor="#000000"><span style="color: black; font-family: Arial; font-size: 10pt;">NOCUOI</span></th> <th bgcolor="#c0c0c0" bordercolor="#000000"><span style="color: black; font-family: Arial; font-size: 10pt;">COCUOI</span></th> </tr>
  </thead> <tbody>
  <tr valign="TOP"> <td bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">110</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">50000</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">0</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">20000</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">6000</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">24000</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">0</span></td> </tr>
  <tr valign="TOP"> <td bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">111</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">150000</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">0</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">10000</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">2000</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">138000</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">0</span></td> </tr>
  <tr valign="TOP"> <td bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">112</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">245000</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">0</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">15000</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">3000</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">227000</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">0</span></td> </tr>
  <tr valign="TOP"> <td bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">113</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">15000</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">0</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">2000</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">11000</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">2000</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">0</span></td> </tr>
  <tr valign="TOP"> <td bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">114</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">85000</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">0</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">3000</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">4000</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">78000</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">0</span></td> </tr>
  <tr valign="TOP"> <td bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">211</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">0</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">20000</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">6000</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">3000</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">
  </span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">11000</span></td> </tr>
  <tr valign="TOP"> <td bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">212</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">0</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">135000</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">7000</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">8000</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">
  </span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">120000</span></td> </tr>
  <tr valign="TOP"> <td bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">213</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">0</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">36000</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">5000</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">1000</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">
  </span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">30000</span></td> </tr>
  <tr valign="TOP"> <td bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">214</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">0</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">42000</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">6000</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">6000</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">
  </span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">30000</span></td> </tr>
  </tbody> <tfoot></tfoot> </table>
  
  Và tblketqua( Chimuc,Congthuc,giatri)
  Mình có các công thức lấy giá trị trên table tblData để cộng trừ nhân chia, và công thức này được lưu trên tblKetqua. Mình muốn tính toán và điền giá trị cuối cùng vào ô giatri trên tblketqua.
  
  
  Đáp:
  Lấy giá trị thông qua hàm Dlookup. Tổng quát, ta viết 1 function để lấy giá trị trên tblData như sau:
  
  

  Mã PHP:

  Function myIndex(Dong As String, Cot As String) As Long
     myIndex = DLookup(Cot, "tblData", "[TK]='" & Dong & "'")
    End Function 


  
  Công thức lưu của bạn phải phát biểu dạng:
  
  

  Mã PHP:

  myIndex("110", "nodau")-(myIndex("113", "PSno") + myIndex("114", "PSCo")) 


  
  Lưu các công thức này vào field Congthuc trên tblketqua.
  
  Ví dụ:
  TBLKETQUA
  <table bgcolor="#ffffff" border="1" cellspacing="0"><span style="color: black; font-family: Arial;"></span><caption><b>TBLKETQUA</b></caption> <thead>
  <tr> <th bgcolor="#c0c0c0" bordercolor="#000000"><span style="color: black; font-family: Arial; font-size: 10pt;">CHITIEU</span></th> <th bgcolor="#c0c0c0" bordercolor="#000000"><span style="color: black; font-family: Arial; font-size: 10pt;">CONGTHUC</span></th> <th bgcolor="#c0c0c0" bordercolor="#000000"><span style="color: black; font-family: Arial; font-size: 10pt;">SOTIEN</span></th> </tr>
  </thead> <tbody>
  <tr valign="TOP"> <td bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">I. TÀI SẢN</span></td> <td bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">myIndex("110", "coCuoi")+ myIndex("111", "coCuoi")+ myIndex("113", "coCuoi")</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">0</span></td> </tr>
  <tr valign="TOP"> <td bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">1. Tài sản cố định</span></td> <td bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">myIndex("111", "noCuoi")-myIndex("112", "noCuoi")</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">0</span></td> </tr>
  <tr valign="TOP"> <td bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">2. Vật liệu</span></td> <td bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">myIndex("110", "nodau")-(myIndex("113", "PSno") + myIndex("114", "PSCo"))</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">0</span></td> </tr>
  <tr valign="TOP"> <td bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">II. NGUỒN VỐN</span></td> <td bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">myIndex("211", "Codau")+myIndex("212", "Codau")+myIndex("213", "Codau")</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">0</span></td> </tr>
  <tr valign="TOP"> <td bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">1. Tỷ lệ vốn chủ sở hữu</span></td> <td bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">myIndex("111", "NoCuoi")</span></td> <td align="RIGHT" bordercolor="#c0c0c0"><span style="color: black; font-family: Arial; font-size: 10pt;">0</span></td> </tr>
  </tbody> <tfoot></tfoot> </table>
  
  Dùng hàm eval để thực thi các công thức dạng chuỗi. Eval(StringExp)
  Run 1 query update để cập nhật giá trị công thức vào ô giá trị:
  
  

  Mã PHP:

  Private Sub cmdTinhKQ_Click()
    SQL = "UPDATE TBLKETQUA SET SOTIEN = Eval([congthuc])"
    DoCmd.SetWarnings False
    DoCmd.RunSQL SQL
    MsgBox " Da update thanh cong"
    DoCmd.SetWarnings True
    DoCmd.OpenTable "TBLKETQUA"
    End Sub 


  
  Chúc thành công:
  Mời xem demo: Download
• Bài trước Bài tiếp