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

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]
esbyrtAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Thanks Paul, and very good point (and excellent article). I too have been burned by exactly what you describe.
0
esbyrtAuthor Commented:
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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
esbyrtAuthor Commented:
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
esbyrtAuthor Commented:
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
esbyrtAuthor Commented:
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
esbyrtAuthor Commented:
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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Your comment isn't an answer to the question, so you should Delete this question instead.
0
esbyrtAuthor Commented:
My comment above answered the question I think. Had the wrong column bound in my list box.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
esbyrtAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.