Oddity Happening with Between SQL Logic

[ekeenan81]

Not sure if you have all come across this but I have basically 12 queries setup for this year for each month, so that when I run I can see who has vacation for that given month.

I have the query for each month validating by for Date where

Between "1/1/2011" And "1/31/2011"

The weird thing here is that for example January and December are fine.

If I run the query for February or June, no dates show up even though vacation is booked for those months.

When I run a general query with no Criteria it showing all dates.

 

[Norie]

Why do you have quotes around the dates?

It should be something like this.

Between #1/1/2011# And #1/31/2011#

I usually enter something like that in long date format, just to make sure I get it right.

eg Between #1 January 2011# And #31 December 2011#

Access will convert this to the short date format.

Another thing you might need to consider is if there is a time portion in your date field.

Also, if you do want to check a date is within a specific year you could use the Year function to extract just the year.

 

[ekeenan81]

Noire,

That was spot on. I had the date field formatted as text which was causing the headache.

I have been updating all of my queries now for by month etc.. and everything seems to be pulling in.

Still trying to get more versed up on Access, so the help is appreciated.

 

[Norie]

Glad it's working.

Just wondering though is it still a text field or is it working now you've changed it to a date field.

Access can do some funny things with dates stored as text, for example it might actually interpret them as 'real' dates.

Which I suppose it some circumstances could be useful.

 

[ekeenan81]

Norie,

The underlying table attributes, I changed my date value from text to date, and had it apply to all forms/queries, so hopefully I am good going forward.

I remember back in Access 97 and 2003 you would get an error, close and re-open and it works.

Fun stuff!

Thanks again