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
Dlookup
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Dlookup
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:
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:
- 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. - 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.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 780
- Joined: 29 Jan 2010, 13:30
Re: Dlookup
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
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