Date and Time Field

[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?

 

[Mike Painter]

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?

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

 

[Deb Struble]

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.

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?

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

 

[John Vinson]

Is the Between statement all inclusive?

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.

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?

I tend to use a criterion like

= CDate([Enter start date:]) AND < DateAdd("d", 1, DateValue([Enter end date:]))

 

[Deb Struble]

Thanks for your help, it works great!

Is the Between statement all inclusive?

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.

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?

I tend to use a criterion like

= CDate([Enter start date:]) AND < DateAdd("d", 1, DateValue([Enter end

date:]))