Error message when coding Access query

[harvestman]

Andrew Smith has kindly given me some suggested coding
for a module to form the basis of a query in Access. See
his reply post for the "TextToDate" coding under Access
Newsgroup Item: "Multiple date formats in a Table" (dated
April 7th). I was able to create the module easily and
successfully run the tests recommended by Andrew, but
when I try to perform the query I get an alert stating:
"Data type mismatch in criteria expression".

I have gone thru my dd-mmm-yyyy date records (currently
recorded as a "text" field) and checked them for
consistency, noting a few glitches in the format of month
component (with e.g., "01" instead of "Jan") and amended
these to be consistent with all the other "mmm" dates,
but I still get this "error" alert message.

Andrew suggested that I might need to do some tweaking...
but I was not sure if he was referring to his coding or
to my original text formatted date setting records.

I have tried doing crosstab queries, but these are
ineffective because of this unresolved date sorting
problem.

See also my responses dated 13-April and 14 april under
original subject heading.

cheers,
harvestman.

 

[Tim Ferguson]

I have gone thru my dd-mmm-yyyy date records (currently
recorded as a "text" field) and checked them for
consistency, noting a few glitches in the format of month
component (with e.g., "01" instead of "Jan") and amended
these to be consistent with all the other "mmm" dates,
but I still get this "error" alert message.

Handling dates in text form like this is A Really Bad Idea. I don't have
any idea what thread you are referring to, but I guess that you were
advised to convert them to proper datetime values.

In the meantime, if you are really keen to use text comparisons, you can do
the whole thing with an explicit cast:

WHERE MyTextDateField = FORMAT(SomeDateValue, "dd\-mmm\-yyyy")

but obviously you won't be able to do any date arithmetic, sorting,
grouping or extraction of DatePart and so on.

See also my responses dated 13-April and 14 april under
original subject heading.

Oh come on: it's only courteous to maintain a single thread under one
heading. :-(

Hope that helps


Tim F