dcount function and nulls, etc.

[Guest]

The following counts all the records in the query called testt where the
“supervisor†field has “rampersad†and the “approved field†is “yesâ€.

x = DCount("[supervisor]", "testt", "[supervisor] = 'rampersad'" & "And" &
"[approved] = 'yes'")

Question, how can I change this so that the†approved†field = “null†or
“not null†or “not like ‘ a particular value’�


Thanks,

 

[Guest]

The domain aggregate functions (DCount, DLookup, etc.) use standard sql where
syntax for their criteria clauses. To check for a null value, use something
like:
x = DCount("[supervisor]", "testt", "[supervisor] = 'rampersad'" & " And
[approved] Is Null")
or
x = DCount("[supervisor]", "testt", "[supervisor] = 'rampersad'" & " And
[approved] Is Not Null")

If you need help creating these criteria clauses, you can make a temporary
query, fill in the criteria, and look at the resulting sql.

Barry

 

[Guest]

Thanks Barry!!

The domain aggregate functions (DCount, DLookup, etc.) use standard sql where
syntax for their criteria clauses. To check for a null value, use something
like:
x = DCount("[supervisor]", "testt", "[supervisor] = 'rampersad'" & " And
[approved] Is Null")
or
x = DCount("[supervisor]", "testt", "[supervisor] = 'rampersad'" & " And
[approved] Is Not Null")

If you need help creating these criteria clauses, you can make a temporary
query, fill in the criteria, and look at the resulting sql.

Barry

The following counts all the records in the query called testt where the
“supervisor†field has “rampersad†and the “approved field†is “yesâ€.

x = DCount("[supervisor]", "testt", "[supervisor] = 'rampersad'" & "And" &
"[approved] = 'yes'")

Question, how can I change this so that the†approved†field = “null†or
“not null†or “not like ‘ a particular value’�


Thanks,