Between does not appear to work

[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

 

[Fredg]

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)