Dcount(ing) records with a blank field

[Peter Bellamy]

I thought I had mastered this before but my VBA code does seem to work now!

I have a line like:
DCount("[table_key]", "Tablename", "[table_date] = Null")

I must have the syntax wrong as it is returning zero despite there being blank [table_date] fields

Thanks

Peter

 

[Rabbie]

A blank field is not necessary Null. if you run a query like

"Select * from [tablename] where isnull(table_date) = True"

do you get any hits.

If so try changing the criteria in your Dcount to "isnull(table_date) = True"

 

[Ron_dK]

You might consider making a query with something like this :

SELECT tablename.table_date
FROM tablename
WHERE (((tablename.table_date) Is Null));


Hth

 

[Peter Bellamy]

Thanks for your replies.

A query testing for a null in that field returns correctly

The "IsNull[table_date] = True" returns a syntax error!

I am actually checking to see if there is a blank/null in that date field to see if there are records to be actioned. The action date stamps the field so that record cannot be used again.

 

[Rabbie]

Sorry about that. Must have had a senior moment. At least rak gave you a solution.

 

[Peter Bellamy]

Not sure I can use rak's solution because I want use a count and make run an If statment against the result.

 

[Ron_dK]

Could you post the query or form where you want this Count to happen.

 

[karl05]

You could try

 SELECT Count(*) AS Expr1
FROM tablename.table_date
WHERE (((IsNull([tablename].[table_date]))<>False));

hth

 

[Peter Bellamy]

The clock is ticking and I needed a solution, so this is what I did.
Testing for = Null did not return anything but testing <> Null gave me a count of all the non null records.
I then counted all the records and subtracted the two results.
Daft, but it works !

Thanks for your suggestions
Peter