Criteria not including today

[Dave]

Access 2003

query results do not seem to include today with the following criteria:

=[forms]![frmReports]![txtStartDate] And
<=[Forms]![frmReports]![txtEndDate]

What have I done wrone with the criteria

Thanks
dave

 

[KARL DEWEY]

Your DateTime field probably include a time element. Time is stored as a
decimal fraction with the date that is the integer part of the number.

Some just add one more day to their criteria. Others remove the time. You
can use a calculated field like this --
MyDateField: DateValue([YourDateField])

Access 2003

query results do not seem to include today with the following criteria:

=[forms]![frmReports]![txtStartDate] And
<=[Forms]![frmReports]![txtEndDate]

What have I done wrone with the criteria

Thanks
dave

 

[fredg]

Access 2003

query results do not seem to include today with the following criteria:

=[forms]![frmReports]![txtStartDate] And
<=[Forms]![frmReports]![txtEndDate]

What have I done wrone with the criteria

Thanks
dave

Most likely your Date Field includes a Time value.
Therefore any data past 00:00:00 AM of the [EndDate] is not within the
Between [FromDate] And [EndDate] criteria.
Easiest work-around is to just add one day manually to the [EndDate]
when prompted, i.e. instead of entering 11/24/2005 enter 11/25/2005.

A better work-around would be to set
[FromDate]
and
[EndDate]
and their expected Datatype as query parameters in the Query Parameter
Dialog (Click Query + Parameter).
Then change your criteria to:
Between [FromDate] and ([EndDate] + 1)

The best solution, if the Time of day is not necessary, is to run an
Update Query to remove the time value from the date:

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

Then make sure the time is not included in any new entries, i.e. use
Date() instead of Now().

 

[John W. Vinson]

Access 2003

query results do not seem to include today with the following criteria:

=[forms]![frmReports]![txtStartDate] And
<=[Forms]![frmReports]![txtEndDate]

What have I done wrone with the criteria

Thanks
dave

Assuming that the field you're searching contains a time component, you'll
only get records up to midnight at the start of txtEndDate - #6/4/2009
11:47:23AM# is in fact NOT <= to #6/4/2009 00:00:00#.

Try a criterion

=CDate([forms]![frmReports]![txtStartDate]) And <DateAdd("d", 1, CDate([Forms]![frmReports]![txtEndDate]))

The CDate will protect you somewhat from oddly entered date formats.

 

[Dave]

Yep - it does have the time also

I will try adjusting by adding one more day

thanks

Dave

Your DateTime field probably include a time element. Time is stored as a
decimal fraction with the date that is the integer part of the number.

Some just add one more day to their criteria. Others remove the time.
You
can use a calculated field like this --
MyDateField: DateValue([YourDateField])

Access 2003

query results do not seem to include today with the following criteria:

=[forms]![frmReports]![txtStartDate] And
<=[Forms]![frmReports]![txtEndDate]

What have I done wrone with the criteria

Thanks
dave