Microsoft Access SQL Date Comparison

Question

I am using Access 2007.

I need to return rows with a date/time field falling within a date range to be specified in query parameters.

The following doesn't error out, but doesn't appear to work.

SELECT FIELDS FROM FOO
WHERE (FOO.CREATED_DTG BETWEEN [START_DTG] And [END_DTG]);

Likewise this doesn't work for me

SELECT FIELDS FROM FOO
WHERE (FOO.CREATED_DTG >= [START_DTG] And FOO.CREATED_DTG < [END_DTG]);

How can I get this to work?

Update: Using CDate doesn't seem to make a difference.

Answer 1

Does your dates start and end with a #?

Answer 2

also you have <= and >= ... you probably only want = on one of these operators.

Answer 3

Is BLAH the name of a field or a table? As you SELECT BLAH I imagine it names a field, but then BLAH.CREATED_DTG makes no sense -- do you mean FOO.CREATED_DTG perchance?

Answer 4

Are you sure the CREATED_DTG field is Date format?

Have you tried

WHERE (FOO.CREATED_DTG BETWEEN #01/01/1971# And #07/07/2009#);

(or whatever is appropriate in the way of dates -- the point is, not a parameter query)

Answer 5

Are [START____DTG] and [END____DTG] fields in the table FOO, or are they parameters? If they are parameters, then you need to declare their type in order to get validation of the input values. If so, you should add this before the first line of your SELECT statement:

PARAMETERS [START_DTG] DateTime, [END_DTG] DateTime;