Between . . .And problem

  • Thread starter Thread starter Simon Jester
  • Start date Start date
S

Simon Jester

My understanding was that if in a query I write: Between[Enter
Beginning Date]And[Enter Ending Date] that the result would be
everything between and including the two dates. For example.
Beginning Date = 04/01/2007 and Ending Date = 04/15/2007. I would
expect to get all records that had a beginning date of
04/01/2007,2,3,4,5,6,7,8,9,10,11,12,23,14,and 04/15/2007. Anyway,
that is my understanding.
Today, however I tried to write such a query and none of the records
that had beginning dates on 04/01/2007 and none of the records that
were on 04/15/2007 appeared in the answer. (There were such records,
by the way). I did get all records from 04/02/2007 through
04/14/2007. So I then tried >=[Enter Beginning Date] And <=[Enter
Ending Date] and got the same results. Those records with a beginning
date of 04/01/07 or 04/15/07 weren't in the result.
At the risk of sounding really dumb, am I doing something wrong?

Any help would be greatly appreciated.

Thanks,
SJ
 
Simon said:
My understanding was that if in a query I write: Between[Enter
Beginning Date]And[Enter Ending Date] that the result would be
everything between and including the two dates. For example.
Beginning Date = 04/01/2007 and Ending Date = 04/15/2007. I would
expect to get all records that had a beginning date of
04/01/2007,2,3,4,5,6,7,8,9,10,11,12,23,14,and 04/15/2007. Anyway,
that is my understanding.
Today, however I tried to write such a query and none of the records
that had beginning dates on 04/01/2007 and none of the records that
were on 04/15/2007 appeared in the answer. (There were such records,
by the way). I did get all records from 04/02/2007 through
04/14/2007. So I then tried >=[Enter Beginning Date] And <=[Enter
Ending Date] and got the same results. Those records with a beginning
date of 04/01/07 or 04/15/07 weren't in the result.
At the risk of sounding really dumb, am I doing something wrong?

Any help would be greatly appreciated.
Click to expand...

If the DateTimes in the table have non-midnight times (formatting doesn't
matter), then that is a common problem for the End Date. You have to add an
extra day because the between is inclusive but only for exactly midnight on the
end date. This should NOT be a problem for the start date though.

Use the parameter dialog to explicitly define your parameters as DateTimes.
Access usually guesses that on the fly, but it never hurts to specifically
indicate that.
 
Back
Top