Between functionality with time stamp misses last date transactions

[EdAROC]

I know this has been asked before...

Created a report which includes a totals query with Max on a time stamp field (transactions entered during the business day) - objective is to 'consider' all transactions for the group to be as of the last transaction date. Example: Multiple shipments of an order would be "classified" based on the last date. Group on Order#, Max on ShipDate (which is a time stamp).

Then there's a query to capture this and other data for the report.

The user selects the date range, on a form, using pop-up calendars, so the date value for the end date is, effectively, like 9/30/06 00:00:00. So, using the Between [form...BeginDate] And [form...EndDate] on the query, all the entries for the last date are not included (unless the last transaction was at exactly midnight).

I'm thinking of standardizing on one of two solutions (out of many):
1. In the VBA coding after dates are checked for being valid add 23:59:59 to the End Date value (which is currently 00:00:00). How do I change the time on a date value?

2. In the query change the end date value in the Between phrase. How do I change that?

 

[PHV]

EndDate + #23:59:59#

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lespaul]

maybe something like:

AND Datefield Between [form...BeginDate] & " 00:00:00" AND [form...EndDate] & " 23:59:59"



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[lespaul]

drats, I was soooooo close! used the & instead of + and forgot the # around the time values!!


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[EdAROC]

Thanks for the suggestions.

The + #23:59:59# worked. And it's simple and straight forward, being in the report query criteria.