Date range in criteria bring back records out of range??

shdale

New member
Local time
Today, 05:29
Joined
Nov 14, 2012
Messages
8
Hello, I have a query where I have the following criteria in a date column:
>=#01/04/2010# And <=#31/03/2011#
So, in effect, I want to bring forward all records where this date is between 1st April 2010 and 31st March 2011
HOWEVER,
My query is bringing back records with dates such as:
29/04/2011
15/03/2012
06/05/2012
I've checked that the format for this filed is in short date format.

Anyone have any ideas why these records are appearing when they should not?

Many thanks, S
 
Instead, you could try

Code:
BETWEEN #01/04/2010# And #31/03/2011#
 
Thanks, just tried this - still same problem...
 
Hmm, try adding a column testDate:Format([yourDateField],"yyyymmdd") that may throw some light if there is something wrong with the date itself.

You should get a field showing the date like 20121116.
 
Thanks, yes all dates seemed to reformat correctly - but still the dates out of range are showing.
 
Hmm. Any other criteria working as an 'OR', ie your dates 'OR' something else?
 
OK, I've found the error - a very sill one too! My criteria included an 'OR' in the criteria - and this was bringing back the records where date out of range. So, all is working okay now.
Thanks for the responses though!
Regards, S
 
I am having a similar but different problem, it seems access can't tell the difference between a day or a month, unless the value is greater than 12, then it knows its a day.

I run a query with this SQL text

"Select * From View_Report_Job_List Where SaleDate>= #" & Date1 & "#"

where Date1 is an input from the user

if the user inputs 5/8/2011, access will give me dates starting from 8/5/2011.

Any ideas?
 

Users who are viewing this thread

Back
Top Bottom