CODEWITHSUNDEEP
sqlms-access-2007
user3715595
user3715595

Reputation: 29

Select from MS Access Table between two dates?

I have an MS ACCESS Table with a string field i use to store dates (I have my reasons to not use a type Date), Is there a way to select rows from the table between two dates ? cuz everything i tried doesnt seem to work, it confuses years, days and months, here is what i tried :

select  *  from audience where Format(auddate, "dd/MM/yyyy") between #01/06/2014# and  #01/08/2014#
select  *  from audience where Format(auddate, "dd/MM/yyyy") > #01/06/2014# and Format(auddate, "dd/MM/yyyy") > #01/08/2014#

among others and i get some meaningless results :

AudDate
25/06/2014
18/09/2012
12/11/2012
28/01/2013
08/02/2011
13/10/2011

Thanks in advance.

Upvotes: 1

Views: 16671

Answers (1)

Mathieu Pagé
Mathieu Pagé

Reputation: 11064

Try CDate() to convert your string into a date.

select  *  from audience 
where CDate(audate) between #01/06/2014# and #01/08/2014#;

If it doesn't work because CDate does not reconize your format you can use DateSerial(year, month, day) to build a Date. You will need to use mid$ and Cint() to build the year, month and day arguments. Something like this for a format "yyyy-mm-dd": 

DateSerial(CInt(mid(audate, 1, 4)), CInt(mid(audate, 6, 2)), CInt(mid(audate, 9, 2))

Hope this helps.

Upvotes: 1

Related Questions