Date and Time Field

  • Thread starter Thread starter Deb Struble
  • Start date Start date
D

Deb Struble

I have a Linked table with a Date/Time Field called TransactionDate. This
field is tracking both the date and time that a particular transaction took
place. In my query I want to pull records that fell between a certain time
frame determined by the user. In the TransactionDate field I have a
parameter set up as Between [Start Date] and [End Date] so the user can
enter a starting and ending date. Is the Between statement all inclusive?
When I enter a Starting Date of 08/01/2003 and the Ending Date of 08/31/2003
I was expecting that it would pull all the transactions for the month of
August, including the transactions that fell on the 1st and the 31st. For
some reason my query does not pull the transactions that took place on the
ending date. Any suggestions?
 
Deb Struble said:
I have a Linked table with a Date/Time Field called TransactionDate. This
field is tracking both the date and time that a particular transaction took
place. In my query I want to pull records that fell between a certain time
frame determined by the user. In the TransactionDate field I have a
parameter set up as Between [Start Date] and [End Date] so the user can
enter a starting and ending date. Is the Between statement all inclusive?
When I enter a Starting Date of 08/01/2003 and the Ending Date of 08/31/2003
I was expecting that it would pull all the transactions for the month of
August, including the transactions that fell on the 1st and the 31st. For
some reason my query does not pull the transactions that took place on the
ending date. Any suggestions?
Click to expand...

I have seen a few situations where the end requires something like mm/dd/yy/
23:59 to get the information for that day.
It does *not* matter how the information is formatted.

It's been several years but the problem was as I recall the way the date was
captured using Now() rather than Date()
 
Thank you for your help! When I typed in the date with the time it worked
perfectly. Do you know of any way to set it so the user would not have to
type in the 23:59, it would just automatically be added to the end date that
they type in. Currently the Start Date and End Date parameters are set up
in the query but I was debating whether it would be better to get the
parameters from a pop-up form where they type in the Start Date and End
Date.


Mike Painter said:
Deb Struble said:
I have a Linked table with a Date/Time Field called TransactionDate. This
field is tracking both the date and time that a particular transaction took
place. In my query I want to pull records that fell between a certain time
frame determined by the user. In the TransactionDate field I have a
parameter set up as Between [Start Date] and [End Date] so the user can
enter a starting and ending date. Is the Between statement all inclusive?
When I enter a Starting Date of 08/01/2003 and the Ending Date of 08/31/2003
I was expecting that it would pull all the transactions for the month of
August, including the transactions that fell on the 1st and the 31st. For
some reason my query does not pull the transactions that took place on the
ending date. Any suggestions?
Click to expand...

I have seen a few situations where the end requires something like mm/dd/yy/
23:59 to get the information for that day.
It does *not* matter how the information is formatted.

It's been several years but the problem was as I recall the way the date was
captured using Now() rather than Date()
Click to expand...
 
Is the Between statement all inclusive?
Click to expand...

Yes... BUT!
When I enter a Starting Date of 08/01/2003 and the Ending Date of 08/31/2003
I was expecting that it would pull all the transactions for the month of
August, including the transactions that fell on the 1st and the 31st.
Click to expand...

A transaction that occurs at #8/31/2003 11:31:28# is NOT "between"
#8/1/03 00:00:00# and #8/31/03 00:00:00# - it's a bit over eleven and
a half hours LATER than your ending date, since a pure date
corresponds to midnight on the selected date.
For
some reason my query does not pull the transactions that took place on the
ending date. Any suggestions?
Click to expand...

I tend to use a criterion like
= CDate([Enter start date:]) AND < DateAdd("d", 1, DateValue([Enter end date:]))
Click to expand...
 
Thanks for your help, it works great!

John Vinson said:
Is the Between statement all inclusive?
Click to expand...

Yes... BUT!
When I enter a Starting Date of 08/01/2003 and the Ending Date of 08/31/2003
I was expecting that it would pull all the transactions for the month of
August, including the transactions that fell on the 1st and the 31st.
Click to expand...

A transaction that occurs at #8/31/2003 11:31:28# is NOT "between"
#8/1/03 00:00:00# and #8/31/03 00:00:00# - it's a bit over eleven and
a half hours LATER than your ending date, since a pure date
corresponds to midnight on the selected date.
For
some reason my query does not pull the transactions that took place on the
ending date. Any suggestions?
Click to expand...

I tend to use a criterion like
= CDate([Enter start date:]) AND < DateAdd("d", 1, DateValue([Enter end
Click to expand...
date:]))
Click to expand...
 
Back
Top