MS Access form date range of single date yields no results-CodePudding

The form uses begin date and end date text boxes formatted as "General Date" to filter results. They are generally filled using the calendar date picker tool as format m/d/yyyy. ODBC SQL table that it's pulling the date field from is also showing format "General Date" in Design View but the field includes date and time as format m/d/yyyy H:MM:SS AM/PM.

When the same date is entered in the form begin and end date it shows no results, but I am told by my team in the past it has shown results. I am assuming that this is due to the form date picker including no time and defaulting to 12 AM in both boxes when the query runs. If that is/may be the case, is there a way to edit the end date text box to default to 11:59:59 PM of the date selected?

CodePudding user response：

I'm not sure how it would play with your ODBC connection, but in the date field in the form under the data tab in the property sheet there is a default value for the form, just like in the MS Access table. You could try to put your default time in there at 11:59:59, it will automatically put quotes around it and it should reflect in your form.

You could also create a default constraint on the database itself to reflect 11:59:59 for the end date if nothing is there. Going this route you would probably need to break up your time and date fields in the database and bring them together in MS Access with a function. That way if a date was placed there with no time it would default to 11:59:59.

CodePudding user response：

is there a way to edit the end date text box to default to 11:59:59 PM of the date selected?

A text box's Default Value property can't do that. But using the text box's After Update event, you could adjust the value the user entered to include the time of day you want. This example adds your desired time value when the text box's time component is midnight (12:00:00 AM).

Private Sub txtEndDate_AfterUpdate()
    If Me.txtEndDate = DateValue(Me.txtEndDate) Then
        ' time component is 12:00:00 AM; replace it
        Me.txtEndDate = Me.txtEndDate   #11:59:59 PM#
    Else
        ' txtEndDate already includes some time other than 12:00:00 AM ...
        ' let it be
    End If
End Sub