asked on

Having trouble with a date range used as a filter

Great Day, I am attempting to print a report that permits the user to select a date range in a form and then the user can either select to open the report using the filter for one customer who used the company's services during that date range or the user can select to open the report and view all customers who have had services performed during that date range.

The code that I am using to create the filter for either all customers or one customers is attached.

The problem I am having is that the code does not seem to work. If, for example, I select a date range from 09/01/2011 to 09/30/2011 for all customers. every record, even those falling outside of the filter are displayed. Using the same dates as an example, if I select to view only one customer, all of the customers still display.

I would like for someone to please show me why the filters are not working.

Thanks in advance,

David

Private Sub btnRepairOrder4One_Click()
On Error GoTo Err_btnRepairOrder4One_Click
    
    ' Filter for one customer
    Dim stDocName As String
    Dim stFilter As String
    Dim stDateRange As String
    Dim sSQLOrder As String
    
    stFilter = "CustomerID= " & Me.txtCustomerID
    stFilter = stFilter & " AND RepairDateOut Between #"
    stFilter = stFilter & Me.txtDateBegin & "# And #" & Me.txtDateEnd & "#"
    stDateRange = Me.txtDateBegin & "# And #" & Me.txtDateEnd & "#"
'
    stDocName = "rpt4CustomerRepairOrders"
    DoCmd.OpenReport stDocName, acPreview, WhereCondition:=stFilter

Exit_btnRepairOrder4One_Click:
    Exit Sub

Err_btnRepairOrder4One_Click:
    MsgBox Err.Description
    Resume Exit_btnRepairOrder4One_Click
End Sub

Open in new window

Private Sub btnRepairOrder4All_Click()
On Error GoTo Err_btnRepairOrder4All_Click


    ' Filter for all customers
    Dim stDocName As String
    Dim stFilter As String
    Dim stDateRange As String
    Dim sSQLOrder As String
  
    stFilter = " RepairDateOut Between #"
    stFilter = stFilter & Me.txtDateBegin & "# And #" & Me.txtDateEnd & "#"
    stDateRange = "#" & Me.txtDateBegin & "# And #" & Me.txtDateEnd & "#"
'
    sSQLOrder = "[RepairDateOut]"
    stDocName = "rpt4CustomerRepairOrders"
    
    DoCmd.OpenReport stDocName, acPreview, WhereCondition:=stFilter
     
    
Exit_btnRepairOrder4All_Click:
    Exit Sub

Err_btnRepairOrder4All_Click:
    MsgBox Err.Description
    Resume Exit_btnRepairOrder4All_Click


End Sub

Open in new window

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mbizup

Also,

Why do you have code for stDateRange? It does not appear to get used in your code...

yddadsjd95

ASKER

Thank you mbizup, You're right, I should have commented out the code for stDateRange. The filter worked for all customers, but I get the following error when I used the code for one customer:

syntax error (missing operator) in query expression 'CustomerID=1
Cdate(RepairDateOut) Between #9/1/2011# And #9/30/2011#'.

this is the code that I used:

stFilter = "CustomerID= " & Me.txtCustomerID
stFilter = stFilter & " CDate(RepairDateOut) Between #"
stFilter = stFilter & CDate(Me.txtDateBegin) & "# And #" & CDate(Me.txtDateEnd) & "#"

Thanks,

David

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Try this:

DoCmd.OpenReport ReportName:=stDocName, View:=acPreview, WhereCondition:=stFilter

SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mbizup

The syntax of the open report statement is ok (either works)...

yddadsjd95

ASKER

Thanks Database, I am still getting the same error.

mbizup

Did you try my last suggestion? http:#a36989542

mbizup

Alternatively, grouping with parentheses, although functionally the same, makes the syntax a little clearer:

stFilter = "CustomerID= " & Me.txtCustomerID
stFilter = stFilter & " AND (CDate(RepairDateOut) Between #"
stFilter = stFilter & CDate(Me.txtDateBegin) & "# And #" & CDate(Me.txtDateEnd) & "#)"

This assumes that CustomerID is numeric.

yddadsjd95

ASKER

Thank you mbizup! I did immediately try your suggested solution and when I went to acknowledge that it worked, the site had shut down. I would like to know what is the difference between your code and the code that I had. It works in another application that I created, but that was created in Access 2002, if that makes a difference, while this one was created in Access 2007.

Thanks again and have a great rest of the evening.

Sincerely,

David

mbizup

Hi David,

<I would like to know what is the difference between your code and the code that I had. It works in another application that I created>

There is almost no difference in functionality between your original code and mine.

The sorting issue that you described happens frequently with dates and numbers. In your case the dates were being sorted alphabetically, like text. There are a couple of solutions for that:

- Explicitly convert your data to a Date (or number) format
OR
- Format your dates and numbers in a way that they will sort properly alphabetically, like adding leading zeros to numbers or formatting dates in a yyyymmdd format.

The reason for the discrepancy in results you noted with other databases is probably that the report in your other database had its data drawn directly from a table where the underlying field was a Date type in the table's design.

Hazarding a guess, the problematic report in this database probably was pulling its data from a query (where a "Date" output may have actually been text).

Btw, in my suggestion I wanted to cover all possibilities by converting everything to Dates:

<<
stFilter = "CustomerID= " & Me.txtCustomerID
stFilter = stFilter & " AND (CDate(RepairDateOut) Between #"
stFilter = stFilter & CDate(Me.txtDateBegin) & "# And #" & CDate(Me.txtDateEnd) & "#)"

>>

But this (converting the report's field but not the textbox values to Date) probably would do the trick as well, and more concisely:

stFilter = "CustomerID= " & Me.txtCustomerID
stFilter = stFilter & " AND (CDate(RepairDateOut) Between #"
stFilter = stFilter & Me.txtDateBegin & "# And #" & Me.txtDateEnd & "#)"