我試圖忽略空白值，下面的代碼采用 dropdown.value 這是一個字串，然后在另一個表中查找該值并在它旁邊拉一個數字。
如果填充了所有下拉串列，則該代碼有效，但是當一個為空時，它會顯示錯誤。
我在網上查看并找到 Nz() 運算式，但它仍然無法正常作業，有沒有辦法忽略我的代碼中的空白下拉串列，或者只是在末尾添加值作為 0？
謝謝
Dim db As DAO.Database
Set db = CurrentDb
Dim Kitchen As DAO.Recordset
Dim strSQLKitchen As String
Dim WC As DAO.Recordset
Dim strSQLWC As String
Dim Bath As DAO.Recordset
Dim strSQLBath As String
Dim ENSuiteA As DAO.Recordset
Dim strSQLENSuiteA As String
Dim ENSuiteB As DAO.Recordset
Dim strSQLENSuiteB As String
Dim Other As DAO.Recordset
Dim strSQLOther As String
'lookup dropdown value and grab a number that resides next to it in a table
strSQLKitchen = "SELECT EOValue FROM Trims WHERE Trim = """ & KitchenTrimType.Value & """"
strSQLWC = "SELECT EOValue FROM Trims WHERE Trim = """ & WCTrimType.Value & """"
strSQLBath = "SELECT EOValue FROM Trims WHERE Trim = """ & BathTrimType.Value & """"
strSQLENSuiteA = "SELECT EOValue FROM Trims WHERE Trim = """ & ENSuiteATrimType.Value & """"
strSQLENSuiteB = "SELECT EOValue FROM Trims WHERE Trim = """ & ENSuiteBTrimType.Value & """"
strSQLOther = "SELECT EOValue FROM Trims WHERE Trim = """ & OtherTrimType.Value & """"
Set Kitchen = db.OpenRecordset(strSQLKitchen)
Set WC = db.OpenRecordset(strSQLWC)
Set Bath = db.OpenRecordset(strSQLBath)
Set ENSuiteA = db.OpenRecordset(strSQLENSuiteA)
Set ENSuiteB = db.OpenRecordset(strSQLENSuiteB)
Set Other = db.OpenRecordset(strSQLOther)
'debug
MsgBox (Nz(Kitchen.Fields(0).Value))
MsgBox (Nz(WC.Fields(0).Value))
MsgBox (Nz(Bath.Fields(0).Value))
MsgBox (Nz(ENSuiteA.Fields(0).Value))
MsgBox (Nz(ENSuiteB.Fields(0).Value))
MsgBox (Nz(Other.Fields(0).Value))
'populate box on form
FormTrimValue.Value = Nz(Kitchen.Fields(0).Value) Nz(WC.Fields(0).Value) Nz(Bath.Fields(0).Value) Nz(ENSuiteA.Fields(0).Value) Nz(ENSuiteB.Fields(0).Value) Nz(Other.Fields(0).Value)
編輯：
錯誤：
'沒有當前記錄'
uj5u.com熱心網友回復：
您只需要一個記錄集和一個陣列即可實作此目的：
Dim Records As DAO.Recordset
Dim Trims(0 to 5) As String
Dim Sql As String
Dim Value As Currency
' Collect trims to look up.
Trims(0) = Nz(KitchenTrimType.Value)
Trims(1) = Nz(WCTrimType.Value)
Trims(2) = Nz(BathTrimType.Value)
Trims(3) = Nz(ENSuiteATrimType.Value)
Trims(4) = Nz(ENSuiteBTrimType.Value)
Trims(5) = Nz(OtherTrimType.Value)
' Retrieve only the trims needed.
Sql = "SELECT EOValue FROM Trims WHERE Trim IN (""" & Join(Trims, """,""") & """)"
Set Records = CurrentDb.OpenRecordset(Sql, dbOpenDynaset, dbReadOnly)
If Records.RecordCount > 0 Then
Records.MoveFirst
' Add those trims found.
While Not Records.EOF
Value = Value = Nz(Records(0).Value, 0)
Records.MoveNext
Wend
End If
Records.Close
' Populate box on form
FormTrimValue.Value = Value
uj5u.com熱心網友回復：
請始終包含錯誤訊息，這有助于更好地了解出了什么問題。除此之外，嘗試將第二個引數傳遞給Nz()- 在這里您必須定義在沒有選擇任何內容時要回傳的記錄。
strSQLKitchen = "SELECT EOValue FROM Trims WHERE Trim = '" & Nz(KitchenTrimType.Value,"") & "'"
PS：不要使用 Trim 作為列名，因為這也是一個函式名，以后只會讓你感到困惑。
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/468364.html
上一篇：盡管它顯示在查詢資料表視圖中，但無法從計數查詢中獲取結果
下一篇：重置沙漏的正確錯誤處理