Screwy Access SQL dates issue...

  • Thread starter Thread starter Brad Pears
  • Start date Start date
B

Brad Pears

I have a query in Access 2000 where I am using the "between" clause to
return rows based on a from and to date. The format for the criteria field
is as follows...

"Between #25-Sep-2005# And #04-Oct-2005#"

What is happeneing is that the query is returning rows from the beginning
date listed there (25-Oct-2005) but is not returning rows up to the ending
date. I am only getting records up to Oct 3rd - the day previous to the
ending date. If I change the criteria to between the #25-Sep-2005# and
#05-Oct-2005#, I will then get the Oct 4th records!!!!

What's up with that?? Is this a known issue? AS far as I know, using the
between clause is supposed to return records between the values listed
INLCUDING the actual values listed in the between clause. In this case it is
including records matching the first date criteria BUT NOT THE 2nd!!!!

Help!

Thanks,

Brad
 
Brad said:
I have a query in Access 2000 where I am using the "between" clause to
return rows based on a from and to date. The format for the criteria field
is as follows...

"Between #25-Sep-2005# And #04-Oct-2005#"

What is happeneing is that the query is returning rows from the beginning
date listed there (25-Oct-2005) but is not returning rows up to the ending
date. I am only getting records up to Oct 3rd - the day previous to the
ending date. If I change the criteria to between the #25-Sep-2005# and
#05-Oct-2005#, I will then get the Oct 4th records!!!!

What's up with that?? Is this a known issue? AS far as I know, using the
between clause is supposed to return records between the values listed
INLCUDING the actual values listed in the between clause. In this case it is
including records matching the first date criteria BUT NOT THE 2nd!!!!
Click to expand...


This is not an issue, but a misunderstanding.

Consider this: A date/time value defaults to midnight when
you do not specify a time part. Thus, #04-Oct-2005# is the
same as #04-Oct-2005 00:00:00#

So, when you compare your date/time field with a time part
you are comparing something like:
#04-Oct-2005 01:23:45# <= #04-Oct-2005 00:00:00#
and it's reasonable for it not to be selected.

Try changing the criteria to
#05-Oct-2005#
or, more precisely
#04-Oct-2005 23:59:59#
 
Absolutely - I completley forgot about the time portion... Thanks for your
help...

Brad
 
Back
Top