Date Filter Problem

[sara1011]

I am having trouble with the query producing the correct Dates

I have the Log Date Field and for the criteria I have

Between Nz([Forms]![frmDate]![txtStart],[LogDate]) An
Nz([Forms]![frmDate]![txtEnd],[LogDate]

When I run the query it asks for Forms!FrmDate!txtStart I ente
10/1/0
It then asks for Forms!FrmDate!txtEnd I enter 10/31/0

The results produced are wrong. It shows me records fro 10/1/05 t
10/29/05 it is not including 10/31/05

Also for Start if I enter 10/26/05 and End I enter 10/26/05 It give
me 0 records when there are 3 records for that date

 

[Chris M]

I am having trouble with the query producing the correct Dates.

I have the Log Date Field and for the criteria I have:

Between Nz([Forms]![frmDate]![txtStart],[LogDate]) And
Nz([Forms]![frmDate]![txtEnd],[LogDate])

When I run the query it asks for Forms!FrmDate!txtStart I enter
10/1/05
It then asks for Forms!FrmDate!txtEnd I enter 10/31/05

The results produced are wrong. It shows me records fro 10/1/05 to
10/29/05 it is not including 10/31/05;

Also for Start if I enter 10/26/05 and End I enter 10/26/05 It gives
me 0 records when there are 3 records for that date.

Trying adding 1 (or maybe 0.99) to your ToDate:

Reason:

How are the dates in the table being created. Are they coming from a form
(ie being entered by a user) or are they being created automatically. If
automatically, is it possible there is a Time element as well as the Date?

Access Dates contain a Date and a Time element. If you represent the date as
a decimal number, the digits before the '.' are the date and the digits
after it are the time.

Eg.

The date in your table could be 10/26/05 (11:32)

Which might be represented as (say) 6554.48

When you are searching 'Between 10/26/05 and 10/26/05'

You will be searching for between 6554 and 6554

Which clearly doesn't include 6554.48

Hope that makes some sort of sense. Say if it doesn't and I'll try and
explain again

(btw I Flippin HATE dates!!! Once spent DAYS trying to teach a Database
application the difference between US dates and UK ones - 1/2/05 - 1st of
Feb? or 2nd of Jan?...)

Chris.

 

[Michel Walsh]

Hi,


You probably have a TIME in addition to the date, in your database. That
implies that 6:00 AM is LATER than at midnight, and thus 10/31/05
6:00:AM is NOT between 10/1/05 00:00:00 and 10/31/05 00:00:00


Hoping it may help,
Vanderghast, Access MVP