Date parameter with Date/Time values (1 Viewer)

nschroeder

nschroeder

Local time

Today, 17:05

Joined

Jan 8, 2007

Messages

186

• Feb 16, 2018

• #1

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

Wino Moderator

Staff member

Local time

Today, 15:05

Joined

Aug 30, 2003

Messages

36,124

• Feb 16, 2018

• #2

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.

 

P

plog

Banishment Pending

Local time

Today, 17:05

Joined

May 11, 2011

Messages

11,638

• Feb 16, 2018

• #3

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

Wino Moderator

Staff member

Local time

Today, 15:05

Joined

Aug 30, 2003

Messages

36,124

• Feb 16, 2018

• #4

plog said:

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.

Click to expand...

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

nschroeder

Local time

Today, 17:05

Joined

Jan 8, 2007

Messages

186

• Feb 16, 2018

• #5

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

AWF VIP

Local time

Today, 23:05

Joined

Jul 26, 2013

Messages

10,366

• Feb 16, 2018

• #6

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

Me.YourStartDate = DateValue(Me.YourStartdate)

 

nschroeder

nschroeder

Local time

Today, 17:05

Joined

Jan 8, 2007

Messages

186

• Feb 16, 2018

• #7

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