Parameter Query Date/Time

[tcarp]

I need the criteria to run a parameter query for date AND time. Using the following works, but I need the time included as well:

Between [Start Date] And [End Date]

I was trying to get something like this to work:

Between #[Start Date]12:01:00 AM# And #[End Date] 11:59:59 PM#

When I manually input the date/time into the criteria box, it works fine, but I am building a macro to partially automate my process. My question is: can someone please provide me with an expression for a query that will prompt the user for the start date and end date, but will automatically include the time (as shown above). The user should only have to change the dates in the query. The time will never change.

Thanks in advance!



TCARP

[Rod]

First thing to remember is that date/time is held internally as a number, the integer part specifying the date and the decimal part specifying the time. I don't think it is necessary to add a time to the start date; I assume you mean midnight. You now want to add all 24 hours less 1 second to the end date. This actually leaves a one-second period between 11:59:59 pm and midnight that is not tested. Personally I would not use the 'Between' construct for this test but do it in two comparisons.

MyDateTime >= CDate([Enter Start Date mdy]) AND MyDateTime < DateAdd("d", 1, CDate([Enter End Date mdy]))

I've used the CDate function rather than the #...# delimiters.