Invalid use of null -- After testing for null

[rando1000]

I'm pulling data from a field on a form, evaluating whether or not it's null, then calculating based on the data in the field. Here's my code for evaluating the field:

intAdvanced = IIf(Not IsNull(Me.txtAd vanced), CLng(Me.txtAdva nced), 0)

So if the field's not null, it Converts it to a Long and sets the variable to that, otherwise, the variable is 0.

However, the program's erroring on this line, Run-Time 94, invalid use of Null! How can I be using null invalid, when, in fact, I'm specifically changing the value to 0 if it's null?

[rando1000]

BTW - Immediate Window

x = Isnull(Me.txtAd vanced)
? x
True

So Access knows the value is null.

[Megalog]

intAdvanced = IIf(IsNull(Me.t xtAdvanced), 0, CLng(Me.txtAdva nced))

or...

intAdvanced = Nz(Me.txtAdvanc ed, 0)

[NeoPa]

An unfortunate side-effect of using IIf() within VBA code (not the same within SQL strangely enough) is that VBA evaluates all parameters before passing them to the function.

I guess you can see now why you got that error in your code :)

Nz() is clearly the better way to code this so you should be fine if you follow MegaLog's suggestion.