Đá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


Có thể liên quan đến chủ đề
Chủ đề: Tác giả Trả lời: Xem: Bài mới nhất
  Hướng Dẫn Xin cách xổ list kết quả khi đánh chữ trong form datasheet tien1505 10 201 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 75 01-07-17, 04:35 AM
Bài mới nhất: ongke0711
  [Help] Giá trị trùng trong report vulhu06 0 150 28-03-17, 02:42 PM
Bài mới nhất: vulhu06
  Làm sao hiện thanh công cụ tranmtam 0 151 09-03-17, 02:47 PM
Bài mới nhất: tranmtam
  [Thủ Thuật] Demo Liệt kê số lượng User đang mở table nào maidinhdan 9 1,925 09-06-16, 09:01 AM
Bài mới nhất: nguyenhungcuong

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ơ