not null dlookup vs. dcount >0

[benjamin.weizmann]

hi,
please say me which is the best way to ensure there are specific record/s in the table

by IF NOT ISNULL (DLOOKUP.......)
or by IF DCOUNT(...)>0

what are the advantages and disadvantages for those?

thanks u
Ben

 

[arnelgp]

Use dcount() it will guarantee a result is returned even if not found (returns 0). While dlookup returns Null when not found.

 

[The_Doc_Man]

Arnel beat me to it.

DCount counts nulls as not there, therefore doesn't count them.

DLookup builds a temporary query and returns the result, which could be NULL if the item you seek isn't present. I.e. it returns exactly what it saw - NULL.

 

[benjamin.weizmann]

Arnel beat me to it.

DCount counts nulls as not there, therefore doesn't count them.

DLookup builds a temporary query and returns the result, which could be NULL if the item you seek isn't present. I.e. it returns exactly what it saw - NULL.

so how can it affects ? are there any specific situations ?

thanks
Ben

 

[The_Doc_Man]

Sorry, Ben... affect what?

If DCount encounters a Null, it doesn't count it... but that means the record wasn't in the table, so you won't get a false positive result.

If DLookup returns a Null, it was because no record existed matching your criteria, so again it means that the desired record wasn't there.

DCount will not return a Null, so you don't have to check for it.

DLookup COULD return a Null, so you have to test the returned value for being Null.