Clever Geek Handbook

Select from MS access table between two dates?

I have an MS ACCESS table with a string field that I use to store dates (I have reasons not to use the Date type). Is there a way to select rows from a table between two dates? Because everything I've tried doesn't seem to work, it's confusing for years, days and months, this is what I've 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 am getting meaningless results:

AudDate
06/25/2014
18.9.2012
11.12.2012
1.28.2013
08/02/2011
13.10.2011

Thanks in advance.

+1


source to share


1 answer


Try CDate()

converting your string to date.

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

If that doesn't work because CDate won't reconfigure your format, you can use DateSerial (year, month, day) to plot the date. You will need to use mid $ and Cint () to create the year, month and day arguments. Something like this for the "yyyy-mm-dd" format:



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

Hope it helps.

+1


source






More articles:


All Articles