Problems querying converted date

[brewerdude]

I'm trying to query and do datediff math on dates in a table that are presented as dd mmm yyyy (05 May 2008). I've used date value to convert this to a normal date, i.e., DateValue([Open Date]).

However, when I try to query on the converted field no matter what date or date range I enter, I get an Date type mismatch in expression.

Also, when entering the date in the Criteria pane, rather then it being changed to #mm/dd/yy#, it just changes it to m/d/y. So if i enter 05/05/08 it converts to 5/5/8. I've tried putting the date in quotes, i.e. >= "05/05/2008", the query runs fine, but it doesn't return the expected result.

Any thoughts?

 

[PHV]

What is the data type of the field in the table ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[brewerdude]

It's text. (It's also a SQL db)

 

[PHV]

The WHERE clause of your SQL code should contains something like this:
DateValue([Open Date])>=#2008-05-05#



Hope This Helps, PH.
FAQ219-2884
FAQ181-2886