DMax Null Error

[JimLecocq]

Hope all are having a good day.

I am running code in VBA to collect data from an Excel file and add that data to a table.

It works fine as long as I have at least one record in the table. But if the table is empty I get an error "Invalid Use of Null" on the following:

Dim MaxDayTest As Integer
MaxDayTest = DMax("[day]", "tbl_Calls_DR") 'Test for last day calls pulled

Does anyone know why or what I can use to test for an empty table before it hits this declaration.

 

[rainman89]

try using the NZ function. it should not throw the error.

 

[JimLecocq]

Tried this but still gets the same error. Thoughts?

MaxDayTest = DMax("nz([day])", "tbl_Calls_DR")

 

[rainman89]

i think maxdaytest is looking for an int and its getting null so maybe

MaxDayTest = NZ(DMax("nz([day])", "tbl_Calls_DR"))

 

[JimLecocq]

Thanks rainman89

I never though of using the nz in the VBA code. Don't know why not it makes sense.

Works perfect now.

 

[rainman89]

glad to help!