[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Having trouble with a date range used as a filter

Posted on 2011-10-18
11
Medium Priority
?
245 Views
Last Modified: 2013-11-27
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

0
Comment
Question by:yddadsjd95
  • 7
  • 3
11 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 36989418
Try converting your data explicity to dates:

    stFilter = " CDate(RepairDateOut) Between #"
    stFilter = stFilter & CDate(Me.txtDateBegin) & "# And #" & CDate(Me.txtDateEnd) & "#"
    stDateRange = "#" & Me.txtDateBegin & "# And #" & Me.txtDateEnd & "#"
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36989427
Also,

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

Author Comment

by:yddadsjd95
ID: 36989518
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75
ID: 36989521
Try this:

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

Assisted Solution

by:mbizup
mbizup earned 2000 total points
ID: 36989542
You're missing an AND:

  stFilter = "CustomerID= " & Me.txtCustomerID
  stFilter = stFilter & " AND CDate(RepairDateOut) Between #"
etc..
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36989553
The syntax of the open report statement is ok (either works)...
0
 

Author Comment

by:yddadsjd95
ID: 36989555
Thanks Database, I am still getting the same error.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36989588
Did you try my last suggestion?  http:#a36989542
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36989694
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.

0
 

Author Closing Comment

by:yddadsjd95
ID: 36989735
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
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36989909
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 & "#)"

0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question