Handling DLookup's Null Return Value

[jeffbruce]

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]

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

 

[jeffbruce]

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]

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

 

[Poppa Smurf]

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]

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