Using CDate and comparing text dates in Access SQL

[atlewis]

Have a perplexing problem. We use an older dBase IV system here and
I've linked to the tables through Access (using Access Developer
Edition 2002).

I have a table of jobs that have a text date field looking like this:

---------------

07\14\2005

---------------

(Yes, this programmer used backslashes).

I wrote a query to find all of the current jobs with a couple of
criteria, and it worked without problems until last week. Nothing about
the query changed, so I guess something about the database changed.
Maybe one of the fields doesn't fit the format now. I've parsed through
the table and haven't found any obvious problems. I've even written a
procedure to step through the table, convert to date, and write
everything back to the table as text again, the same way it was, using
the format() function.

Here is the query. The CDate part is what's breaking it, because if I
take that out it works with the rest intact.

---------------

SELECT j.JICODE, i.IC_CODE, i.IC_NAME, j.JINAME
FROM jobname AS j, icust AS i
WHERE (((i.IC_CODE)= j.jgencon)
AND ((CDate(Replace([j].[jbdate],"\","/")))>CDate(Now()))
AND ((j.JINAME) Not Like "*\1 Original")
AND ((j.JBDATE) Not Like "12\31*")
AND ((j.MARK) In (1,4)))
ORDER BY i.IC_NAME, j.JINAME;

---------------

Thanks for any suggestions.

Regards,

Andy

 

[Arvin Meyer]

If it worked until last week you might be able to determine the condition
which changed the behavior and fix it. As a work around you can use the
Replace() function in an alias column in an Access query to reformat the way
your date field looks. Then join your query to the new date field as the
field you use the criteria against.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access