CODEWITHSUNDEEP
sqlms-accessdatetime
Ben Aston
Ben Aston

Reputation: 55729

Microsoft Access SQL Date Comparison

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.

Upvotes: 0

Views: 8054

Answers (5)

David-W-Fenton
David-W-Fenton

Reputation: 23067

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;

Upvotes: 1

Smandoli
Smandoli

Reputation: 7019

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)

Upvotes: 1

Alex Martelli
Alex Martelli

Reputation: 881567

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?

Upvotes: 2

John Nicholas
John Nicholas

Reputation: 4836

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

Upvotes: 1

Anthony
Anthony

Reputation: 923

Does your dates start and end with a #?

Upvotes: 1

Related Questions