non zero / is null problems - Error 94 - invalid use of null

[Keith Nichols]

Lots and lots of posts on this subject so it is something that obviously gives lots of trouble to newbies such as myself.

The code below works fine if the control is not null but gives Error 94 - invalid use of null when the control is null. I have gone around in circles with this - what am I doing wrong?

PrjNumber = Me!Projectnumber
        If IsNull(PrjNumber) Then
            MsgBox "Project number is null "
        Else: PrjNumber = " project " & PrjNumber
            MsgBox "Project number is not null "
        End If

 

[gemma-the-husky]

the error is occurring at the first statement - if projectnumber is null this line will fail immediately.

[COLOR="Red"]'this produces an erorr immediately
PrjNumber = Me!Projectnumber[/COLOR]

i assume prjnumber is a string, from your subsequent use so instead use nz function and try

if nz(projectnumber,0)=0 then
            MsgBox "Project number is null "
Else PrjNumber = " project " & projectnumber
            MsgBox "Project number is not null "
End If

 

[Keith Nichols]

Thanks Gemma,

I shall go a way and play!

 

[gemma-the-husky]

i've just noticed an error in my code, and modified it slightly

 

[Keith Nichols]

Thanks for the heads up.

 

[Keith Nichols]

Thanks Gemma,

This worked for me:

        If Nz(Projectnumber, 0) = 0 Then                'Test for null project number
                    PrjNumber = ""
            Else: PrjNumber = " Project: " & Projectnumber
        End If