Select from MS Access Table between two dates?

Question

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.

And please do not use day month year format, it is very dangerous, use year month day. — Fionnuala
– Fionnuala, Commented Jul 8, 2014 at 11:38
you are both right, it makes no sense now, thanks for the advices :) — user3715595
– user3715595, Commented Jul 10, 2014 at 15:35

Mathieu Pagé · Accepted Answer · 2014-07-08 01:23:39Z

1

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.

answered Jul 8, 2014 at 1:23

Mathieu Pagé

11.1k13 gold badges54 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Select from MS Access Table between two dates?

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related