Handling DLookup's Null Return Value (1 Viewer)

jeffbruce · May 21, 2009

Hi everybody,

I'm using VB and my code is below:

quantityRegion = DLookup("[Quantity]", "tblSalesData", "[regionCode] = " & i & " AND [Year] = " & yr)

If (IsNull(quantityRegion)) Then
'nothing
Else
'bunch of code
...
End If

Sometimes quantityRegion results in a Null value, and apparently I don't know how to deal with it. How can one maintain this control logic while replacing the line If (IsNull(quantityRegion)) Then with an alternate (correct) chunk of code? Any suggestions?

pbaldy · May 21, 2009

You can probably declare quantityRegion as a Variant instead of whatever you've currently declared it.

jeffbruce · May 21, 2009

Awesome, your suggestion worked. Cheers.

I guess I always assumed that if you didn't explicitly define (Dim) a variable in VB, it would be implicitly defined at compile-time as type Variant. I was wrong.

pbaldy · May 21, 2009

Actually that should be true according to VBA help, but personally I always declare them explicitly.

Poppa Smurf · May 21, 2009

You could use the following code

If IsNull(DLookup("[Quantity]", "tblSalesData", "[regionCode] = " & i & " AND [Year] = " & yr)) Then
'nothing
Else
'bunch of code
...
End If

Fuse3k · May 21, 2009

You can also use the nz() function. Nz() replaces a null value with a specified value.

Nz(DLookup("[Quantity]", "tblSalesData", "[regionCode] = " & i & " AND [Year] = " & yr), 0)

This would return 0 if [Quantity] was null.

hth

Handling DLookup's Null Return Value (1 Viewer)

jeffbruce

Registered User.

pbaldy

Wino Moderator

jeffbruce

Registered User.

pbaldy

Wino Moderator

Poppa Smurf

Registered User.

Fuse3k

Registered User.

Similar threads

Users who are viewing this thread