Invalid use of null -- After testing for null

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.txtAdvanced), CLng(Me.txtAdvanced), 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?

re: Invalid use of null -- After testing for null

BTW - Immediate Window

x = Isnull(Me.txtAdvanced)
? x
True

So Access knows the value is null.

re: Invalid use of null -- After testing for null

intAdvanced = IIf(IsNull(Me.txtAdvanced), 0, CLng(Me.txtAdvanced))

or...

intAdvanced = Nz(Me.txtAdvanced, 0)

re: Invalid use of null -- After testing for null

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.