Date Syntax

[creyn]

Is this the proper syntax for date in Access?

Between #01/01/2016# And #01/31/2016#


If so, why does it continue to default to this when I run the query?

Between "#01/01/2016#" And "#01/31/2016#"


I am using Access 2013

 

[Norie]

Is the field you are setting this criteria for a Date field?

Where are you actually putting/using this criteria?

 

[creyn]

No, they have it set as a Short Text field. How can I use the Between #01/01/2016# And #01/31/2016#? Will I need to change the field to a date field?

 

[Norie]

Rather than altering the table structure you could try using CDate to convert the field values from string to dates but ideally it should be a Date field.

 

[creyn]

I went ahead and changed it to a Date field. It worked. Thanks. I appreciate it

 

[james_lankford]

you also might want to do


Between #01/01/2016# And #02/1/2016#

to make sure you get the last day of January
I forget which way is correct

 

[Joe4]

you also might want to do


Between #01/01/2016# And #02/1/2016#

to make sure you get the last day of January
I forget which way is correct

No, you wouldn't want to do that.
Between is inclusive (think greater than or equal to, less than or equal to)

So if you did it that way, it would also pick up February 1.

The only thing to consider is if you have a time component to your dates, then you probably want to use something like:

>= #1/1/2016# and < #2/1/2016#

That will pick up everything from midnight 1/1/2016 up to 1/31/2016 11:59:59 PM.
Otherwise, you would miss anything that happened on 1/31/2016 after midnight.
(Leaving off the time component is a value of 0:00:00 AM, or Midnight).