Can't get 'Between' to work for dates!

[Dynamo418]

Hi all,

I am trying to do something I thought was simple: only include table items between dates for one of my fields (group by date).

The dates are in this format (example from table):

2005-08-24 22:11:00.000000

I used this SQL line to try and do this:

HAVING (((TABLE.A)=1) AND ((TABLE.B)=2) AND ((TABLE.C) Between #1/1/2013 12:0:0# And #5/1/2013 12:0:0#))

However it did not work: Table A and B groups worked fine, but I still get dates from 2005!

Any ideas?

[Dal Jeanis]

Try

Between #1/1/2013 12:00:00# And #5/1/2013 12:00:00#))

[pbaldy]

Also confirm that the data type of the field is Date/Time rather than text.

[Dal Jeanis]

Yes, my code does assume that .C is actually stored as a date.

If not, then use something like

DateValue(TABLE.C) Between #1/1/2013 12:00:00# And #5/1/2013 12:00:00#

[Dynamo418]

Ah I determined that Table.C is stored as text and not date/time. Unfortunately this is locked and I cannot modify. I tried to use

DateValue(TABLE.C) Between #1/1/2013 12:00:00# And #5/1/2013 12:00:00#

but I got the error message: Date type mismatch in criteria expression.Is there another format I can use to create a between statement? It doesn't need to be date format, it just needs to work like a date format if that makes sense!

[Dynamo418]

Figured it out to use (TABLE.C) Between '1/1/2013 12:00:00' And '5/1/2013 12:00:00' seems to work!

[Dal Jeanis]

Yes, but that's doing a text comparison. Try between '1/1/2012' AND '5/1/2013' and you'll see what's wrong.

Try

DateValue(TABLE.C) Between #1/1/2013# And #5/1/2013#