Prompt User for a date in a query using date time field

Question

I am trying to create a simple query that prompts the user for a date to filter records by the date entered.  The date field unfortunately is a date time field.  If I go to a record and delete the time from the date time field, it returns those records.  I need the time to remain for other purposes.  What do I need to put in my Criteria besides [Enter the date you want to produce a report for]

Answer 1

Create an extra column in the query:

D: Int([DateTimeField])

where DateTimeField is the name of the field you want to filter on.

Clear the Show check box in this new column, and place the parameter prompt [Enter the date you want to produce a report for] in the Criteria row of this column instead of under DateTimeField itself.

---

Regards, Hans Vogelaar

Answer 2

Instead of Hans' suggestion (which will definitely work!), an alternative is to change your criteria to

BETWEEN [Enter the date you want to produce a report for] AND DateAdd("d", 1, [Enter the date you want to produce a report for])

or

BETWEEN [Enter the date you want to produce a report for] AND DateAdd("s", 86399, [Enter the date you want to produce a report for])

Be careful to ensure that you've typed the name of prompt identically in the two places so that you don't get two prompts! (copy-and-paste is usually the best way to do this)

The advantage of this second approach is that the function call to DateAdd only gets run once, whereas the Int function will be applied to every row in the table.

<picky>
It would probably be better to use the DateValue function than the Int function, in case you have dates before 30 Dec, 1899 in the table
</picky>

---

Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/djsteele (no e-mails, please!)
Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

Answer 3

One very-very little addition.

BETWEEN [Enter the date you want to produce a report for] AND DateAdd("d", 1, [Enter the date you want to produce a report for])

It assumes that if you want to get all the data for October, 16th, you'll also get a data for 10.17.2011 0:00:00. It isn't important for the most part of applications, but sometimes it may give a piece of extra data. So I'd make an open interval at the right (not sure about English equivalent):

[DateTimeField] >= [Enter the date you want to produce a report for] AND [DateTimeField] < DateAdd("d", 1, [Enter the date you want to produce a report for])

BTW, I see this fact is taken into account in the second suggestion about using DateAdd("s", 86399, ...)

---

Andrey V Artemyev | Saint-Petersburg, Russia
Russian blog artemyev.biztoolbox.ru
English blog enblog.biztoolbox.ru

Answer 4

Very true, Andrey.

If the date field is populated using the Now function, the probability of a given entry having a time of exactly 00:00:00 isn't that high, but it's certainly worth considering.  As you noted, the alternative I provided of adding 86399 seconds (1 second less than a full day) will handle that as well as your suggestion does.

---

Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/djsteele (no e-mails, please!)
Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

Answer 5

My HERO!  Thank you very much.

Answer 6

Just for completeness, the overriding reason to use Andrey's or Doug's second approach is that using a function such as Int or DateValue on the date/time field makes it impossible for the query to be optimized based on the date/time field being indexed.