How to return 0 if blank

Question

I'm trying to ignore blank values, the below code takes dropdown.value which is a string, then look up that value and pull a number next to it in a different table.

The code works if all dropdowns are populated, but when one is empty it shows an error.

I looked online and found the Nz() expression but it still isn't working, is there a way to ignore blank dropdowns in my code OR just add the value at the end as a 0?

Thank you

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)

Edit:

Error:

'No Current Record'

Answer 1

You need only one recordset and an array to achieve this:

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