Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Filter form to open a report, one criteria isn't working and how to add a date range?

Posted on 2014-07-15
15
Medium Priority
?
248 Views
Last Modified: 2014-10-07
I have a form with unbound controls to filter a report a variety of ways. If I select one or more Service Codes and Bee Types the report filter properly but if I add a filter for province it returns no records. Can someone spot an error in the code?

Second part of the question - I have two date boxes to filter for a date range but I'm not sure of the syntax to add that to the filter being built.
Thanks for having a look!

filter form
Private Sub cmdApplyFilter_Click() 'Open Report button
    Dim varItem As Variant
    Dim strBeeType As String
    Dim strSvcCode As String
    Dim strProvince As String
    Dim strFilter As String
    Dim strSortOrder As String
    
' Check that the report is open
    If SysCmd(acSysCmdGetObjectState, acReport, "rptSamplePOSNotDestroyed") <> acObjStateOpen Then
        DoCmd.OpenReport "rptSamplePosNotDestroyed", acViewReport
        Exit Sub
    End If
' Build criteria string from lstSvcCode listbox
    For Each varItem In Me.lstSvcCode.ItemsSelected
        strSvcCode = strSvcCode & ",'" & Me.lstSvcCode.ItemData(varItem) _
        & "'"
    Next varItem
    If Len(strSvcCode) = 0 Then
        strSvcCode = "Like '*'"
    Else
        strSvcCode = Right(strSvcCode, Len(strSvcCode) - 1)
        strSvcCode = "IN(" & strSvcCode & ")"
    End If
' Build criteria string from lstBeetype listbox
    For Each varItem In Me.lstBeeType.ItemsSelected
        strBeeType = strBeeType & ",'" & Me.lstBeeType.ItemData(varItem) _
        & "'"
    Next varItem
    If Len(strBeeType) = 0 Then
        strBeeType = "Like '*'"
    Else
        strBeeType = Right(strBeeType, Len(strBeeType) - 1)
        strBeeType = "IN(" & strBeeType & ")"
    End If
' Build criteria string from lstProvince listbox
    For Each varItem In Me.lstProvince.ItemsSelected
        strProvince = strProvince & ",'" & Me.lstProvince.ItemData(varItem) _
        & "'"
    Next varItem
    If Len(strProvince) = 0 Then
        strProvince = "Like '*'"
    Else
        strProvince = Right(strProvince, Len(strProvince) - 1)
        strProvince = "IN(" & strProvince & ")"
    End If
    'Build date criteria string
    'strDateRange = "RecDate between '" & Me.txtStartDate & "' AND '" & Me.txtEndDate & "'"
    
' Build filter string

    strFilter = "[SvcCode] " & strSvcCode & _
                " AND [BeeType] " & strBeeType & _
                " AND [Province] " & strProvince
               
' Build sort string
    If Me.cboSortOrder1.Value <> "Not Sorted" Then
        strSortOrder = "[" & Me.cboSortOrder1.Value & "]"
        If Me.cmdSortDirection1.Caption = "Descending" Then
            strSortOrder = strSortOrder & " DESC"
        End If
        If Me.cboSortOrder2.Value <> "Not Sorted" Then
            strSortOrder = strSortOrder & ",[" & Me.cboSortOrder2.Value & "]"
            If Me.cmdSortDirection2.Caption = "Descending" Then
                strSortOrder = strSortOrder & " DESC"
            End If
            If Me.cboSortOrder3.Value <> "Not Sorted" Then
                strSortOrder = strSortOrder & ",[" & Me.cboSortOrder3.Value & "]"
                If Me.cmdSortDirection3.Caption = "Descending" Then
                strSortOrder = strSortOrder & " DESC"
                End If
            End If
        End If
    End If
' Apply filter and sort to report
    With Reports![rptSamplePosNotDestroyed]
        .Filter = strFilter
        .FilterOn = True
        .OrderBy = strSortOrder
        .OrderByOn = True
    End With
    
    With Reports![rptSamplePosNotDestroyed]
    .Filter = strFilter
    .FilterOn = True
    .txtReportTitle.Value = _
        "Sample Report - " _
        & vbCrLf & "Service Codes: " & Me.lstSvcCode.Value _
        & vbCrLf & "Bee Types: " & Me.lstBeeType.Value _
        & vbCrLf & "Provinces: " & Me.lstProvince.Value
End With
End Sub

Open in new window


[Code embedded by SouthMod]
0
Comment
Question by:esbyrt
  • 7
  • 6
14 Comments
 
LVL 85
ID: 40198313
Nothing jumps out at me, but the best way to troubleshoot these things is to print the value of your query's SQL to the Debug window, and post it here. Just do this:

' Apply filter and sort to report
Debug.Print strFilter <<<<<<<<<<<<<<<
Debug.Print strSortOrder <<<<<<<<<<<<<
    With Reports![rptSamplePosNotDestroyed]
        .Filter = strFilter
        .FilterOn = True
        .OrderBy = strSortOrder
        .OrderByOn = True
    End With

And post the values from the Immediate window.
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 40198319
Regarding the Date Range: Your code should work, although Access generally expects hash marks as deliimiters, so try this:

'Build date criteria string
    'strDateRange = "RecDate between #" & Me.txtStartDate & "# AND #" & Me.txtEndDate & "#"

You can also use > and < operators:

    'strDateRange = "RecDate > #" & Me.txtStartDate & "# AND RecDate <" & Me.txtEndDate & "#"
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40198390
You can also use > and < operators:
actually you should use  >=  then  <

but you need to add one day to the end date

 'strDateRange = "RecDate >= #" & Me.txtStartDate & "# AND RecDate <" & Me.txtEndDate plus 1 day & "#"

see: "Beware of Between"

no points please, others already did the work.
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
LVL 85
ID: 40198899
Thanks Paul, and very good point (and excellent article). I too have been burned by exactly what you describe.
0
 

Author Comment

by:esbyrt
ID: 40200697
Here is the result from the immediate window.  I had 2 provinces selected and none show. The province list box is based on a query with one column only and the bound column is 0 for the listbox. All the province selections show properly in the listbox, just not in the built filter.

Immediate window - [SvcCode] IN('NC','NI') AND [BeeType] IN('Honey Bee','Mason Bee') AND [Province] IN('','') AND [DateRange] RecDate >=#01-Mar-14# AND RecDate <= #31-Mar-14#

I added this string for the dates:
strDateRange = "RecDate >=#" & Me.txtStartDate & "# AND RecDate <= #" & Me.txtEndDate & "#"

And added this is the build filter section
' Build filter string

    strFilter = "[SvcCode] " & strSvcCode & _
                " AND [BeeType] " & strBeeType & _
                " AND [Province] " & strProvince & _
               " AND [DateRange] " & strDateRange

But when I run it like that I get a runtime error 3075 Syntax error missing operator in query expression.
Thanks for having a look!
0
 
LVL 85
ID: 40200753
Somehow you're not picking up the selected values in lstProvice. Place a breakpoint on line 37, and run the code again. When the code enter break mode, see if you're actually working through the Listbox items by stepping through the code.

When you hit line 41, go to the Immediate window and type this:

?strProvince

and hit the Enter key, and report back what the Immediate window shows.

Continue stepping through the code, and let us know what's happening in the IF statement that begins on Line 41. Does the code fall to the IF portion or the ELSE portion?
0
 

Author Comment

by:esbyrt
ID: 40205644
Okay I set the break and stepped through. Here's the results.
?strProvince
,'',''
At line 40 it jumps back to line 37. Then to 40, 41 then the Else on 43. After that is steps through in order.
0
 

Author Comment

by:esbyrt
ID: 40243945
Hi. Well I have been busy packing and moving to a different city but I do still need an answer to this one. Are you still there?
0
 

Assisted Solution

by:esbyrt
esbyrt earned 0 total points
ID: 40353239
Figured out the answer for my lstProvince box. I had the wrong column bound. However I am still having trouble getting the filter to apply. I will post a new question.
0
 

Author Comment

by:esbyrt
ID: 40353328
I've requested that this question be closed as follows:

Accepted answer: 0 points for esbyrt's comment #a40353239

for the following reason:

Resolved the problem on my own.
0
 
LVL 85
ID: 40353329
Your comment isn't an answer to the question, so you should Delete this question instead.
0
 

Author Comment

by:esbyrt
ID: 40353352
My comment above answered the question I think. Had the wrong column bound in my list box.
0
 
LVL 85
ID: 40356674
My comment above answered the question I think. Had the wrong column bound in my list box.
Okay, but this is the exact reason why you should not ask multiple questions in the same post. While your solution resolved the first part, it did nothing for the second.

eenookami - I have no further issue with the author accepting their own comment.
0
 

Author Closing Comment

by:esbyrt
ID: 40365513
My apologies Scott. I hadn't gone back and read through all the comments. You did indeed give me the answer for the date criteria. I have accepted your answer for that one. Thanks.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
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

577 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