Đá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
  [Thủ Thuật] Demo Liệt kê số lượng User đang mở table nào maidinhdan 9 1,313 09-06-16, 09:01 AM
Bài mới nhất: nguyenhungcuong
  Lấy về số seri CPU, ổ cứng MainBoard trong Access Noname 33 12,288 08-06-16, 11:23 PM
Bài mới nhất: maidinhdan
  [Thủ Thuật] Ẩn record trong table theo ngày tháng tvn_hut 6 486 24-04-16, 01:00 AM
Bài mới nhất: tvn_hut
  Hàm đọc số dùng mã Unicode trong Access Noname 28 11,700 23-03-16, 11:45 PM
Bài mới nhất: thiennamlong
  Tùy chọn liên kết lại các table từ file data có password Noname 37 14,803 21-01-16, 10:59 AM
Bài mới nhất: minhhoacatv

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ơ