date ranges in Access

  • Thread starter Thread starter Dave Cullen
  • Start date Start date
D

Dave Cullen

I have a table column defined as text data, formatted as dates in the
form "MM/DD/YYYY". I need to query this table for records of date range.
The results I get do not match my criteria. For instance if I ask for >
01/01/2005 I get back records from 2002.

I've tried creating a calculated column using CDate and get the same
results when setting criteria on that. I've also used the # sign
delimiters with no difference.

Help please.
 
If it is TEXT, then I would not expect your query to work.

If you want to find items in a date range, you would need to use a DATE
field.

There are functions you can use to convert the string to a date value and
use that.
 
If you can't use a real date field (which would be the best solution) then
you are on the right track with CDate(). Here's an example. In this example
I've used a condition to filter out any records where the 'DateAsText' field
contains a Null value, because a Null value is not a valid argument for the
CDate() function. An alternative would be to use the NZ() function to
convert Null values to some other value. Which approach is more appropriate
would depend on your specific requirements. Of course if there are no Null
values, this is a moot point.

Also watch out for any non-Null values that can not be converted to valid
dates.

SELECT CDate([DateAsText]) AS DateFromText
FROM tblTest
WHERE (((tblTest.DateAsText) Is Not Null))
ORDER BY CDate([DateAsText]);
 
Back
Top