DCount only counting some dates

[Jules48]

I've attached a "database" which illustrates the problem I'm having. I'm trying to count the number of records containing a particular date. The Dcount appears to count some dates but ignores others. For example, if I ask it to count records containing the date 14/10/2010 it works, but if I ask it to count those with the date 04/06/2010 it returns zero.



A query finds the records OK but still Dcount won't, even when the query is used as the data source. I've spent more than 2 days solid trying to make this work and am now desperate.

The syntax I've used for Dcount is:
DCount("*", "tblTest","ECDate = #04/06/10#") but I've messed about with lots of variations of the syntax.

This must be something simple - I can't believe Access will accept some dates but ignore others. I've "hard coded" the dates into the DCount function for testing but in the database I'm developing the dates are passed to the function via parameters.

I'll be enormously grateful if someone can shed some light on this for me.

Many thanks,

Jules

[RuralGuy]

You may find this like helpful: http://allenbrowne.com/ser-36.html

[Jules48]

Thanks RG. It does appear that the problem lies in the difference between American and British date formatting. If I ask Dcount to count the records containing 06/04/2010 (which is not in the table) it returns a count. If I ask it to count 04/06/2010 (British format, which IS in the table) it returns 0.

This seems a major flaw in Access and I'm surprised it hasn't become more prominent in the fora.

I'll have to try and think of a fix, maybe by converting the date to text and searching for that text string????

Oh well, back to the drawing board!

Thanks again RG.

Jules

[RuralGuy]

It has been an issue with I believe *all* versions of Access. How about doing as Allen suggest and convert your dates to US format for the user?

[RuralGuy]

If it helps, here is how the data is actually stored: http://support.microsoft.com/kb/q130514/en-us

[Jules48]

Thanks again RG.
Plenty for me to get my teeth into there!

Cheers!

[RuralGuy]

It would be easy to create a procedure that would put the date in US format if you were *certain* it was in British format since we are only certain when you get past the 12th day of the month.

[Jules48]

There's some really useful stuff in the links you sent me RG.

Thanks. I'll let you know how I get on!

Jules