CODEWITHSUNDEEP
sqlms-accessdatetimems-access-2007
tecno
tecno

Reputation: 99

SQl queries searching by date range

I have a table in an Access 2007 database. All fields are of type text. Can the following be done using the where clause? If so how?

The usual < <= operators don't seem to work.

Thanks,

Upvotes: 2

Views: 15890

Answers (4)

Alexey Khoroshikh
Alexey Khoroshikh

Reputation: 435

Mr.David-W-Fenton is right saying that the first one SELECT * from Table1 WHERE ColumnDate between '2010-03-26' and '2010-03-19' by Marcelo doesn't work, and wrong suggesting the cause. The clause is incorrect because of quotes around string representations of dates, the date format is pretty OK. So I'd assume

SELECT * from Table1 WHERE CDATE(ColumnDate) between #2010-03-26# and #2010-03-19#

as a proper solution.

Upvotes: 0

Codesleuth
Codesleuth

Reputation: 10541

SELECT * from Table1 WHERE (CDATE(ColumnDate) BETWEEN #03/26/2010# AND #03/19/2010#)
SELECT * from Table1 WHERE (CINT(ColumnAge) between 25 and 40)

Dates are represented in Access between # symbols in #MM/DD/YYYY#. You should really be storing the date as a date field :)

Upvotes: 6

Anton Gogolev
Anton Gogolev

Reputation: 115701

Try converting ColumnDate to actual date/time with CDate function. Conversion to int can be done with CInt, I guess.

I don't use Access, so it's just a common-sense guess.

Upvotes: 1

Marcelo Cantos
Marcelo Cantos

Reputation: 185842

SELECT * from Table1 WHERE ColumnDate between '2010-03-26' and '2010-03-19'
SELECT * from Table1 WHERE ColumnAge between 25 and 40

I don't use Access, so YMMV.

Upvotes: 1

Related Questions