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'

Are you getting an error message on the debug Msgbox stattements? Or just when you try to populate the box on the form? It looks to me like you're not testing for EOF on your recordset - so the error message should be on the debug statements - before you even get to the populate line. — dbmitch, Apr 19 '22 at 19:51
Its on the 'populate box on form bit. The code works fine, it just breaks when all dropdowns aren't filled out. — Tom TK, Apr 19 '22 at 20:18
You must have a record with an empty TRIM value then. What does it look like in the Immediate Window when you change MsgBox to Debug.Print? `No Current Record` indicates an EOF condition. — dbmitch, Apr 19 '22 at 20:26
That's my problem, how can I ignore empty Trim value? Nz doesn't seem to work :( — Tom TK, Apr 20 '22 at 01:29
You want 0 to show instead of "" for blank fields? NZ doesn't apply to empty fields. — dbmitch, Apr 20 '22 at 16:14

score 0 · Answer 1 · answered Apr 19 '22 at 19:40

0

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.

answered Apr 19 '22 at 19:40

Attila

118
1
8

I have added the error now and taken your points on board. This still happens when Nz is added to the dropdown. Its working with all boxes populated but error still applies on empty ones. – Tom TK Apr 19 '22 at 20:17

Gustav · Accepted Answer · 2022-04-21T19:56:12.080

0

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

edited Apr 21 '22 at 19:56

answered Apr 20 '22 at 07:32

Gustav

53,498
7
29
55

Thanks Gustav. I have put this in my database and tried to reverse engineer it, Its not working for some reason, but there's no error to debug. Its being called when a trimtype's dropdown is actioned. Its supposed to add all these numbers together and put them in FormTrimValue.Value. Any ideas? – Tom TK Apr 21 '22 at 17:32
1

Perhaps you need a `MoveFirst`. But have in mind please, that this is air code, so debugging is left to you. So, check the Sql, count of records, the loop, etc. – Gustav Apr 21 '22 at 19:58

How to return 0 if blank

2 Answers2