Đánh giá chủ đề:
  • 5 Votes - 3 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Tính giá trị 1 công thức lưu trong table
#1
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 StringCot 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
Chữ ký của Noname 020
ღღღღღTài sản của Noname (View All Items) ღღღღღ
Reply
Những người đã cảm ơn Khanhls


Có thể liên quan đến chủ đề
Chủ đề: Tác giả Trả lời: Xem: Bài mới nhất
Lightbulb [Hỏi] Ghi lại mọi thay đổi diễn ra trong access toidjtjmtoi 2 153 19-09-17, 11:06 AM
Bài mới nhất: toidjtjmtoi
  [Thủ Thuật] [Hàm] Lấy địa chỉ IP của máy vi tính hiện hành maidinhdan 0 77 09-09-17, 11:47 PM
Bài mới nhất: maidinhdan
  Hướng Dẫn Xin cách xổ list kết quả khi đánh chữ trong form datasheet tien1505 10 375 02-07-17, 01:23 PM
Bài mới nhất: tranthanhan1962
  [Thủ Thuật] Table variables - Lưu các biến hệ thống vào bảng ongke0711 0 161 01-07-17, 04:35 AM
Bài mới nhất: ongke0711
  [Help] Giá trị trùng trong report vulhu06 0 203 28-03-17, 02:42 PM
Bài mới nhất: vulhu06

Chuyển nhanh:


User(s) browsing this thread: 1 Guest(s)
Diễn Đàn Thơ Văn Thi Ẩm Lâu|Nhà Hàng Sông Thơ