Date parameter with Date/Time values

[nschroeder]

I have a field in a table containing date/time values, and want to use a parameter query with a form from which the user can enter a value in a date picker textbox. When a date is entered, however, no matching records are found because of the time in the field values.

I can get it to work by putting two date fields on the form for a beginning and ending date range, and then enter today's date in the beginning field and tomorrow's in the ending field. It will then include all of today's records, but is there a simpler way?

 

[pbaldy]

In your query, use DateAdd() on the end date textbox to add a day to whatever is entered. If times could include midnight, you might want to add minutes or seconds instead.

 

[plog]

Yes, extract just the date portion using DateValue (https://www.techonthenet.com/access/functions/date/datevalue.php). That way you only have to use one input.

 

[pbaldy]

Yes, extract just the date portion using DateValue (https://www.techonthenet.com/access/functions/date/datevalue.php). That way you only have to use one input.

The danger in that is it can be very inefficient. If you have:

...WHERE DateValue(FieldName) = Whatever

the DateValue() function must be applied to every record in the table.

 

[nschroeder]

I see your point. I also have another problem. I have the date picker textbox defined with a format of Short Date, and that's the way it appears when the field doesn't have focus, but if I click in it, it shows up as date/time. Not only is it ugly, but it affects the query results, because it will only include records after the current time value. In other words, if the current time is 10:56 and I select 12/1/2017 in the date picker, it will only include records between 12/1/17 10:56 AM and 12/2/17 10:56 AM (using your DateAdd suggestion).

 

[Minty]

Put an after update event to tidy it up, then your query will still be efficient, something like

Me.YourStartDate = DateValue(Me.YourStartdate)

 

[nschroeder]

I found the problem. In FormLoad, I had tbReportDate = Now() instead of Date(). My bad!