Search field for Dates

[Topflite66]

I have a form that I would like to have a combo box that I can do searches for the order date. Below is the code I am using for the AfterUpdate combo.

Private Sub cboWholesaleDateSearch_AfterUpdate()
If IsNull(cboWholesaleDateSearch) Then
Me.FilterOn = False
Else


Me.Filter = "[WhlSleOrderID]=" & cboWholesaleDateSearch & ""
Me.FilterOn = True
End If
End Sub


When I enter or select the date in the search box I receive the message "The value you entered isn't valid for this field." I have the Format options set to Medium Date for both the search box and the text box in the main form. I am using the same code for several other search boxes on another form, however they are not searching for dates, but they work fine. Thank you.

[June7]

If you want to filter on date, why is field searching in WhlSleOrderID and not a date/time field?

Use # delimiter for date/time field parameter. Like:

Me.Filter = "[yourdatefield]=#" & cboWholesaleDateSearch & "#"

[Topflite66]

If you want to filter on date, why is field searching in WhlSleOrderID and not a date/time field?

When I was working on the code for the other form, if I had the field I was searching for listed, I would get a Run-Time error 3709. Another user suggested I use the OrderID field and that fixed the issue. I just used the same code and changed the fields. I should mention that for the Search boxes for the other form, I have the Row Source as a query with the OrderID and search item referenced.

I made the changes you suggested. I tried adding the # delimiter and then changing from [WhlSleOrderID] to [WhlsleDate]. Received the same message.

Private Sub cboWholesaleDateSearch_AfterUpdate()
If IsNull(cboWholesaleDateSearch) Then
Me.FilterOn = False
Else
Me.Filter = "[WhlsleDate]=#" & cboWholesaleDateSearch & "#"
Me.FilterOn = True
End If
End Sub

I also have the Row Source as the query with WhlSleOrderID and WhsleDate. I was thinking that as long as I establish Medium Date format in both the search box and also the date field in the form I would be fine. Apparently that's not the case.

[June7]

But you said you are searching for dates, not IDs.

If your combobox value is ID, then your original code should work. If you want to provide db for analysis, follow instructions at bottom of my post.

[Topflite66]

I re-copied and pasted the code again and for some reason this time it worked. Thank you for your help.