Dlookup quirk

[MrAustin]

Hey all,

I'm unsure as to how to catch this error -- when using a dlookup function to find a user_id to match up records in another dlookup function, it works fine unless there is no record to match.

Here is the line:

intUserID = DLookup("[user_id]", "[tblUsers]", "[user_login] = '" & strProvidedLogin & "'")

Now that will work fine if the strProvidedLogin matches something in the tblUsers table, but produces an Invalid Use of Null error if there is no match. How do I get around this?

I'm trying to make my own login form from scratch

Thanks!

 

[ShiftyJaffa]

Use NZ to zero the null value
eg:-
intUserID = NZ(DLookup("[user_id]", "[tblUsers]", "[user_login] = '" & strProvidedLogin & "'"))

If intUserID = 0 then msgbox "Invalid":' do something else

Hope that helps

Paul

 

[Pat Hartman]

That's not necessarily correct. When you use Nz() on a numeric field, the default is 0. However when you use Nz() on a text field, the default is a zero-length string. In either case, it is best provide your own default to avoid confusion.

intUserID = Nz(DLookup("[user_id]", "[tblUsers]", "[user_login] = '" & strProvidedLogin & "'"), 0)

 

[ShiftyJaffa]

Yes, sorry my post assumed intUserID was an integer