DATE/TIME MATH ISSUE!!!!

[Mike S. S.]

I have a query that filters the table rows based on the following criteria
that go against a date/time field: (user fills in date when prompted; I
hard-coded to test)

<=#11/30/2008# And >=#11/30/2008#-7

I would have expected all rows where the date is between 11/24/08 and
11/30/08 inclusive. However, what I get is values between 11/23/08 and
11/29/08. I can understand that I should be subtracting 6 instead of 7, but
I don't at all get why my syntax is NOT including rows where the date =
11/30/08. I have checked my formula and the field values carefully and to
return the range of rows I want, I need to say:

<=#12/1/08# and >= #12/1/08# -7 or

<=#11/30/08#+1 and >= #11/30/08#-6.

Can someone explain why the very first formula does not work?????? HELP!!!

 

[Rick Brandt]

I have a query that filters the table rows based on the following
criteria that go against a date/time field: (user fills in date when
prompted; I hard-coded to test)

<=#11/30/2008# And >=#11/30/2008#-7

I would have expected all rows where the date is between 11/24/08 and
11/30/08 inclusive. However, what I get is values between 11/23/08 and
11/29/08. I can understand that I should be subtracting 6 instead of 7,
but I don't at all get why my syntax is NOT including rows where the
date = 11/30/08. I have checked my formula and the field values
carefully and to return the range of rows I want, I need to say:

<=#12/1/08# and >= #12/1/08# -7 or

<=#11/30/08#+1 and >= #11/30/08#-6.

Can someone explain why the very first formula does not work??????
HELP!!!

Because your dates also include times. Only midnight exactly will match
#11/30/08#. All other times of the day are after that and thus not
included in your results. The usual work-around is to add an extra day
on the last value.

 

[a a r o n _ k e m p f]

SQL Server has explicit DATE and TIME fields.

Maybe you should choose a database that meets your needs!