Criteria not including today

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

Access 2003

query results do not seem to include today with the following criteria:
=[forms]![frmReports]![txtStartDate] And
<=[Forms]![frmReports]![txtEndDate]
Click to expand...

What have I done wrone with the criteria

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])

Dave said:
Access 2003

query results do not seem to include today with the following criteria:
=[forms]![frmReports]![txtStartDate] And
<=[Forms]![frmReports]![txtEndDate]
Click to expand...

What have I done wrone with the criteria

Thanks
dave
Click to expand...
 
Access 2003

query results do not seem to include today with the following criteria:
=[forms]![frmReports]![txtStartDate] And
<=[Forms]![frmReports]![txtEndDate]
Click to expand...

What have I done wrone with the criteria

Thanks
dave
Click to expand...

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().
 
Access 2003

query results do not seem to include today with the following criteria:
=[forms]![frmReports]![txtStartDate] And
<=[Forms]![frmReports]![txtEndDate]
Click to expand...

What have I done wrone with the criteria

Thanks
dave
Click to expand...

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]))
Click to expand...

The CDate will protect you somewhat from oddly entered date formats.
 
Yep - it does have the time also

I will try adjusting by adding one more day

thanks

Dave

KARL DEWEY said:
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])

Dave said:
Access 2003

query results do not seem to include today with the following criteria:
=[forms]![frmReports]![txtStartDate] And
<=[Forms]![frmReports]![txtEndDate]
Click to expand...

What have I done wrone with the criteria

Thanks
dave
Click to expand...
Click to expand...
 
Back
Top