dlookup using null criteria 2

[ftook]

Im trying to count how many records have a null entry in field [credit], ive tried :

Me.var1 = DCount("[Pono]", "PO Returns", "[credit] = ''"))

[credit] is a text field

doesnt seem to count correctly - can anyone help please ?

 

[Ed2020]

[credit] = '' will count records where the field is empty, not null (there is a difference).

You'll need to use

Me.var1 = DCount("[Pono]", "PO Returns", "[credit] Is Null"))

Ed Metcalfe

Please do not feed the trolls.....

 

[ftook]

Thank you Ed - worked great ***

 

[Ed2020]

You're welcome.

BTW - The difference between the two can be regarded as:

Null - Value is unknown.
Empty ("") - No value for this record.

Sometimes you will want to count one or the other value, sometimes you will want to count both.

Ed Metcalfe.

Please do not feed the trolls.....

 

[ftook]

ive extended the function, and it now gives type mismatch

If DCount("[Pono]", "PO Returns", "[credit] Is Null" And "[pono] = " & Me.pno) = 0

pono is integer

where am i going wrong

 

[MajP]

This where criteria needs to be a string so:

"[credit] Is Null And [pono] = " & Me.pno

with AND outstide the parentheses it looks to vba as a variable