Between does not appear to work

  • Thread starter Thread starter Neill M. Pinkney
  • Start date Start date
N

Neill M. Pinkney

Hi

Access 2000 NT 4.0

I have a form containing a button that runs a report based
on a query and two unbound edit boxes StartDate and
EndDate. Both have an input mask 00/00/0000;0;_ and are
formatted as ShortDate. StartDate has a default value
=DateSerial(Year(Date()),Month(Date()),1)). Enddate has a
default value Date().

In the query I have a criteria for the date:-
Between Forms![formname]![StartDate] and Forms![formname]!
[EndDate].

Why will it not pull up data that falls in the range?

TIA
 
Neill,
What data is missing?
Is it only records that fall on the last date?
If so, then your date field is storing the date and TIME value.
8/10/2003 is not the same as 8/10/2003 08:15:00 AM
If your criteria is
Between 8/1/2003 and 8/20/2003
only records up to, and including, 8/20/2003 00:00:00 AM (midnight) will be
returned.

To retrieve the entire last date records, enter the date and time value, in
this case 8/20/2003 23:59:59

Might be better to run an update query and remove all the Time values from
the field, retaining only the Date value.

Update YourTable Set YourTable.DateField = Int([DateField]);

By the way, this expression you are using as default for
the control has one too many closing parenthesis.

=DateSerial(Year(Date()),Month(Date()),1))
Should be
=DateSerial(Year(Date()),Month(Date()),1)
 
Back
Top