Simple date range selection

  • Thread starter Thread starter DaveM
  • Start date Start date
D

DaveM

I have just upgraded to Access 2003 and seem to have hit
some sort of bug. All I want to do is run a query to
select records that fall between two dates. The selection
criteria consistantly does not select records from the
last day of the range. I have tried it several ways as
follows:
Between DateSerial(2004,1,1) And DateSerial(2004,1,31)
Between [Type starting date: ] And [Type ending date: ]
=DateSerial(2004,1,1) And <=DateSerial(2004,1,31)
Click to expand...
Is this a bug or am I doing something really stupid?

Thanks for any comments - DaveM
 
Dear Dave:

A likely problem is that your data contains not only a date but also a
time. This is one of the features of a date/time datatype.

So, when you say "between 1/1/2003 and 1/10/2003" without giving any
time of day, the computer must add a time of day. It uses 0 for the
time of day, meaning midnight. Thus, any time of day after midnight
on 1/10/2003 is not in the range you specified.

Now, if this is the cause of your problem, one way of looking at it is
to ask why you are not also specifying the time of day, rather than
letting it default to a value you may not want. Alternatively, you
could specify the time of day to be used when none is entered, making
it 23:59:59.99 when none is entered, thus including the entire day at
the end of the period specified, rather than giving it the meaning of
the first millisecond of the day and no more.

Hope this gives you good perspective on the problem.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


I have just upgraded to Access 2003 and seem to have hit
some sort of bug. All I want to do is run a query to
select records that fall between two dates. The selection
criteria consistantly does not select records from the
last day of the range. I have tried it several ways as
follows:
Between DateSerial(2004,1,1) And DateSerial(2004,1,31)
Between [Type starting date: ] And [Type ending date: ]
=DateSerial(2004,1,1) And <=DateSerial(2004,1,31)
Click to expand...
Is this a bug or am I doing something really stupid?

Thanks for any comments - DaveM
Click to expand...
 
Back
Top