Invalid use of Null (Access 97)

[Beady]

Can somebody please tell me what is wrong with this code:

userID = DLookup("[UserID]", "users", "([userLogon]= '" & Forms!frmLogon!srcUserLogon & "' AND [userPassword] = '" & Forms!frmLogon!srcuserPassword & "')")
If isNull(userID) Then
etc....

It works fine if the username and password are correct, but if not I get a message 'Invalid Use of Null'.
userID is an Autonumber, that I use as a primary key in the user table.

I've also tried with:

userID = 0
userID = DLookup("[UserID]", "users", "([userLogon]= '" & Forms!frmLogon!srcUserLogon & "' AND [userPassword] = '" & Forms!frmLogon!srcuserPassword & "')")
If userID = 0 Then
etc.....
Which also gets an 'Invalid Use of Null' but only when the username/password are incorrect.
The code is some from a database I did in 1999, where it works fine. I can't see why it does this.

 

[Moniker]

UserID is apparently a numeric field, and you cannot set a numeric field to a NULL. If the UserID = NULL (the DLookup fails), you will get the "Invalid Use of Null" error.

Put a wrapper around the Dlookup, like so:

userID = Nz(DLookup("[UserID]", "users", "([userLogon]= '" & Forms!frmLogon!srcUserLogon & "' AND [userPassword] = '" & Forms!frmLogon!srcuserPassword & "')"),"0")

This will convert the NULL to a 0. You can then check for a 0 (like in your second example), which is what you'll get when the UserID/password combination is invalid.

 

[PeterF]

If you use

Dim userID as integer

you get this error because you assign a null value to something that can't be null.
You should have used

Dim userID as variant

Edit: Moniker was faster and I do like his solution.