syntax of between for dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Okay! Heres the deal. I have tried both of these in my query criteria. The
dates coming in are 10/01/2005 and 10/31/2005. The results show 10/01/2005
to 10/30/2005. I am missing 10/31/2005. I have Access 2003. Yes, I have
checked and the data is there in my database, it just does not show in the
query. There is no other criteria that would make it dissapear. Is there a
bug in my version or did I do something wrong?

Between ([Forms]![frm400ReportbyDate]![FromDate]) And
([Forms]![frm400ReportbyDate]![ToDate])

Or
=[Forms]![frmReportbyDate]![FromDate] And <=[Forms]![frmReportbyDate]![ToDate]
Click to expand...


Thanks for you assistance,
Denise
 
Okay! Heres the deal. I have tried both of these in my query criteria. The
dates coming in are 10/01/2005 and 10/31/2005. The results show 10/01/2005
to 10/30/2005. I am missing 10/31/2005. I have Access 2003. Yes, I have
checked and the data is there in my database, it just does not show in the
query. There is no other criteria that would make it dissapear. Is there a
bug in my version or did I do something wrong?

Between ([Forms]![frm400ReportbyDate]![FromDate]) And
([Forms]![frm400ReportbyDate]![ToDate])

Or
=[Forms]![frmReportbyDate]![FromDate] And <=[Forms]![frmReportbyDate]![ToDate]
Click to expand...


Thanks for you assistance,
Denise
Click to expand...

Okay. Here's the answer.

Your Date Field includes a Time value.
Therefore any data past 00:00:00 AM of the[ToDate] is not within the
Between [FromDate] And [ToDate] criteria.
Easiest work-around is to just add one day to the [ToDate] when
prompted.

A better work-around would be to set
[Forms]![frm400ReportbyDate]![FromDate]
and
[Forms]![frm400ReportbyDate]![ToDate]
and their expected Datatype as query parameters in the Query Parameter
Dialog (Click Query + Parameter).
Then Change your criteria to:
Between [Forms]![frm400ReportbyDate]![FromDate] and
([Forms]![frm400ReportbyDate]![ToDate] + 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 TourTable Set YourTable.[DateField] = int([DateField]);

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

I could be that the values in the table include time components. When you
have a [ToDate] of 10/31/2005, the time component of this is zero, meaning
midnight, the first moment of that day. So, this would exclude all the rest
of that day.

Looking at the date/time values in this column without formatting them to
show only the date component, but showing also the time component, may go a
long way to solving this mystery.

Now, if you use DateAdd to add 1 day to the value on your form, and change
the <= to just <, that may fix the problem, assuming it is caused in the way
I described.

Tom Ellison


Deniseksds said:
Okay! Heres the deal. I have tried both of these in my query criteria.
The
dates coming in are 10/01/2005 and 10/31/2005. The results show
10/01/2005
to 10/30/2005. I am missing 10/31/2005. I have Access 2003. Yes, I have
checked and the data is there in my database, it just does not show in the
query. There is no other criteria that would make it dissapear. Is there
a
bug in my version or did I do something wrong?

Between ([Forms]![frm400ReportbyDate]![FromDate]) And
([Forms]![frm400ReportbyDate]![ToDate])

Or
=[Forms]![frmReportbyDate]![FromDate] And
<=[Forms]![frmReportbyDate]![ToDate]
Click to expand...


Thanks for you assistance,
Denise
Click to expand...
 
DateOrd: DateValue([dbo_orders.ordDate])
I was able to solve the problem by first having a query select the data and
create a field with just a date.

I then have a query with the between phrase that allows for the date
selection.

Thanks for all your assistance.

Deniseksds said:
Okay! Heres the deal. I have tried both of these in my query criteria. The
dates coming in are 10/01/2005 and 10/31/2005. The results show 10/01/2005
to 10/30/2005. I am missing 10/31/2005. I have Access 2003. Yes, I have
checked and the data is there in my database, it just does not show in the
query. There is no other criteria that would make it dissapear. Is there a
bug in my version or did I do something wrong?

Between ([Forms]![frm400ReportbyDate]![FromDate]) And
([Forms]![frm400ReportbyDate]![ToDate])

Or
=[Forms]![frmReportbyDate]![FromDate] And <=[Forms]![frmReportbyDate]![ToDate]
Click to expand...


Thanks for you assistance,
Denise
Click to expand...
 
Back
Top