How to return 0 if blank-CodePudding

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'

CodePudding user response：

Please always include the error message, that helps to better understand what went wrong. Beside that, try to pass the second parameter to Nz() - here you have to define which record to be returned when there is nothing selected.

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

P.S.: do not use Trim as a column name, as that is also a function name which will just confuse you later on.

CodePudding user response：

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   
    ' 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