Dlookup

User avatar
silverback
5StarLounger
Posts: 771
Joined: 29 Jan 2010, 13:30

Dlookup

Post by silverback »

I keep falling foul of "invalid use of null" when using DLookup.
There are situations where I need to use DLookup but the contents of a field are allowably null.
It seems to be the assignment to a variable in the clause "variable = Dlookup(......)" which actually fails, as help tells me that DLookup will return null if nothing matches the criterion.
How can I use DLookup to find out if a field is null without generating the error, please?
Or should I be doing it another way?
Thanks
Silverback

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Dlookup

Post by HansV »

A variable of type String, Long, Boolean etc. can not hold Null values, so if you have code like this

Dim strName As String
strName = DLookup(...)

you'll get an error message if DLookup returns Null because there is no match. There are two ways around it:
  1. You can declare the variable as a Variant; the Variant data type can hold Null values:

    Dim varName As Variant
    varName = DLookup(...)

    If there is no match, varName will be Null.
  2. Alternatively, you can use the Nz function to replace Null with a value that you specify. Examples:

    Dim strName As String
    strName = Nz(DLookup(...), "")

    If DLookup returns Null, strName will be set to the empty string "".

    Dim lngQuantity As Long
    lngQuantity = Nz(DLookup(...), 0)

    If DLookup returns Null, lngQuantity will be set to 0.
Of course, you can specify another value instead of "" or 0.
Best wishes,
Hans

User avatar
silverback
5StarLounger
Posts: 771
Joined: 29 Jan 2010, 13:30

Re: Dlookup

Post by silverback »

Wow - what a powerful function Nz is. It's absolutely what is needed and will be pressed into service immediately.
Thank you for explaining so thoroughly - your explanation is very clear, concise and helpful.
The point about variant is also worth remembering, so thanks for that also.
Silverback