Screwy Access SQL dates issue...

[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

 

[Marshall Barton]

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!!!!

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#

 

[Brad Pears]

Absolutely - I completley forgot about the time portion... Thanks for your
help...

Brad