Thanks to the help of this site I am continuing to improve on some queries I have.
An issue I am trying to resolve now is to get a date range working. I have two unbounded textboxes on a form for the user to enter in a start date and end date (GetSDate, and GetEDate).
I also created a 3rd unbounded text to store the results of what is entered by the user (DateSrch).
I want the user to have several options:
1. If no dates are entered bring back all records
2. If a Start Date and End Date are entered, bring back all records between (inclusive)
3. If only a Start Date is entered bring back all records on that date and forward
4. If only a End Date is entered bring back all records on that date and before.
In the form, I built into a run button the following code that populates the "DateSrch" textbox:
Dim strWhere As String
If IsDate(Me.GetSDate) Then
strWhere = ">=" & "#" & Me.GetSDate & "#"
If IsDate(Me.GetEDate) Then
If strWhere <> vbNullString Then
strWhere = strWhere & " And "
strWhere = strWhere & "<=" & "#" & Me.GetEDate & "#"
Me.DateSrch = strWhere
This part is working fine and is displaying the different choices as expected:
>=#1/1/2013# And <=#1/5/2013#
In the query I have the following statement under the Criteria:
Leaving the dates empty brings back all the records as expected. Entering any dates causes the query to halt with an error. If I copy and paste the data displayed in the "DateSrch" directly to the Criteria of the query it brings back the correct results as well.
I'm sure what I am trying to do should work but I can't see where I am going wrong?
Any help would be greatly appreciated.