CODEWITHSUNDEEP
vbams-access
goxarad784
goxarad784

Reputation: 435

Invalid use of Null Access VBA even when IIF condition states not to check

I am trying to create a date query so that null is returned if there is no date and only date value from date/time is pulled if there is date and time value

IIf(Nz(rst("DateAssigned"), "") = "", "NULL", "'" & DateValue(rst("DateAssigned")) & "'")

Now, I get the error:

Run time error: '94' Invalid use of Null

When the rst("DateAssigned") value is null. I know this is coming from the DateValue function. Why is it trying to parse the DateValue when the value is null?

Does vba parse everything in the entire statement without checking the IIF condition? Or did I make a mistake?

Upvotes: 0

Views: 228

Answers (3)

AHeyne
AHeyne

Reputation: 3455

The answer to your questions

Why is it trying to parse the DateValue when the value is null?

and

Does vba parse everything in the entire statement without checking the IIF condition? Or did I make a mistake?

is:

Because DateValue is used as an argument for a parameter of the Iif procedure.

In general:

A procedure can only be executed when all its parameters are known. Therefore, procedures that are given as arguments are executed first.

Upvotes: 0

Gustav
Gustav

Reputation: 55816

You must make sure, that no invalid values are passed to DateValue:

IIf(IsNull(rst("DateAssigned"), "Null", "'" & DateValue(Nz(rst("DateAssigned"), Date()) & "'")

But is would be more straight forward to use Format:

IIf(IsNull(rst("DateAssigned")), "NULL", Format(rst("DateAssigned"), "Short Date"))

Upvotes: 0

Vlado
Vlado

Reputation: 888

I think you are right: VBA parses the entire IIF statement first before run it. Try this in immediate window:

?IIf(1 = 1, "NULL",  DateValue(NULL))

You will get the same error.

I suggest you use the regular IF statment (if you do not want to use it inside of a query)

Upvotes: 1

Related Questions