如果空白，如何返回0

Question

我试图忽略空白值，以下代码取回下拉值。值是一个字符串，然后查找该值并将其旁边的数字拉到其他表中。

如果所有下拉列表都填充，则代码可以工作，但是当一个空名时，它会显示错误。

我在线查看并找到了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)

编辑：

错误：

'没有当前记录'

Answer 1

您只需要一个记录集和一个数组即可实现这一目标：

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

Answer 2

请始终包含错误消息，该消息有助于更好地了解出了什么问题。除此之外，尝试将第二个参数传递到nz（） - 在这里，您必须定义在没有选择的情况下返回的记录。

strSQLKitchen = "SELECT EOValue FROM Trims WHERE Trim = '" & Nz(KitchenTrimType.Value,"") & "'"

PS：请勿将Trim用作列名，因为这也是函数名称，以后会混淆您。