Validate DLookup, DCount, DSum Data Returned

[dpimental]

I am trying to find the most efficient way to validate the data returned from a DLookup, DCount or DSum. Although DLookup is easy, since it is an empty string..,

Is the best method simply the following ...

if DCount("[sales]","orders","[city] = 'Boston'") > 0 then
Msgbox "The Count of Orders for Boston is " & DCount("[sales]","orders","[city] = 'Boston'")
end if

if DSum("[sales]","orders","[city] = 'Boston'") > 0 then
MsgBox "The Total Sales for Boston is " & DSum("[sales]","orders","[city] = 'Boston'") 
end if

Is there a more efficient way to validate the DCount and DSum?

David Pimental
(US, Oh)

 

[Craig0201]

What do you mean by validate?

Speed wise, you making the calls twice unnecessarily.

Dim dblResult As Double

dblResult = DCount("[sales]","orders","[city] = 'Boston'")

if dblResult > 0 then
Msgbox "The Count of Orders for Boston is " & dblResult
end if

dblResult = DSum("[sales]","orders","[city] = 'Boston'")

if dblResult > 0 then
MsgBox "The Total Sales for Boston is " & dblResult
end if

 

[dpimental]

Actually, I meant error handling.

For example if nothing is returned, then the variable assigned to DCount, DSum or DLookup would be null. That would produce an error such as invalid use of null.

David Pimental
(US, Oh)

 

[Craig0201]

dblResult = Nz(DCount("[sales]","orders","[city] = 'Boston'"),0)

is what you're after then.

Nz replaces null with the second parameter.

 

[dpimental]

cool, thanks. That's just what I needed.

David Pimental
(US, Oh)