Suggestions on how to fix search form error?

[templeowls]

I have a form that I'm using to filter a report. The user inputs the date range and then can type in a category, and the form will filter a report.

The VBA for the short text field is below and I can't seem to code it correct. I'm getting a 3075 error on the sFilter line. Any suggestions?

    If Not IsNull(Me.txtComplaintCategory) Then
                  sFilter & "[ComplaintCategory] ='" & Me.txtComplaintCategory & "'"
    End If

I can provide more code if needed.

[xps35]

Switch the & and =

[CJ_London]

You may also need an ‘ and ‘ before [complaintcategory]

[orange]

I agree with the other responses. Also, if you had ComplaintCategory in a combo, then you might eliminate some spelling or typos if that is an issue.

[Welshgasman]

Then again you could just Debug.Print sFilter to see the error?

[templeowls]

Switch the & and =

Which '&' exactly?

[moke123]

 sFilter = "[ComplaintCategory] = """ & Me.txtComplaintCategory & """"

But I dont think thats right. Aren't you also filtering by a date range also?

I would expect something like (aircode)

Dim strFilter as string

strFilter = "MyDateRangeField between #" & startDate & "# and # " & endDate & "#"

if not isnull(Me.txtComplaintCategory ) then
strFilter = strFilter & " and [ComplaintCategory] = """ & Me.txtComplaintCategory & """"
end if

me.filter = strfilter
me.filteron = true

[Welshgasman]

My thoughts are that the category needs to be added to the existing filter (which is date criteria?)

[xps35]

Which '&' exactly?

Sorry, the first.

[Micron]

Why switch? Not this??

sFilter = "[ComplaintCategory] ='" & Me.txtComplaintCategory & "'"

I don't see any evidence of trying to incorporate a date in the posted filter so I left that alone. I've grown tired of posts with only error numbers so if that one has something to do with a date then ignore me.

[June7]

The user inputs the date range and then can type in a category, and the form will filter a report.

OP is building a multi-part criteria string. I don't see anything wrong with posted code. Possibly sFilter is lacking AND operator. Without previous code, can't really know. Review http://allenbrowne.com/ser-62.html

Agree, complaints should be selected from combobox unless want to do pattern matching in which case need LIKE and wildcards instead of =. If there is a standardized set of complaints then should save a ComplaintID into data and use that value in search.

Should provide exact error message, not just error number.

[templeowls]

OP is building a multi-part criteria string. I don't see anything wrong with posted code. Possibly strFilter is lacking AND operator. Without previous code, can't really know. Review http://allenbrowne.com/ser-62.html

Agree, complaints should be selected from combobox unless want to do pattern matching in which case need LIKE and wildcards instead of =. If there is a standardized set of complaints then should save a ComplaintID into data and use that value in search.

Should provide exact error message, not just error number.

Sorry everyone. Here's the full code.

Private Sub btnSearch_Click()

    Dim frm As Form
    Dim sFilter As String
    
    Set frm = [Forms]![frm_complaints]
    
    If Len(sFilter) <> 0 Then
        sFilter = "[ComplaintNumber] IN (" & sFilter & ") AND "
    End If
    
    If Not IsNull(Me.txtComplaintCategory) Then
        sFilter = sFilter & " and [ComplaintCategory] = """ & Me.txtComplaintCategory & """"
    End If
    
    If IsDate(Me.txtStartDate) Then
        sFilter = sFilter & "[ComplaintDate] >= #" & Format$(Me.txtStartDate, "YYYY-MM-DD") & "# AND "
    End If
    
    If IsDate(Me.txtEndDate) Then
        sFilter = sFilter & "[ComplaintDate] <= #" & Format$(Me.txtEndDate, "YYYY-MM-DD") & "# AND "
    End If
    
    If Len(sFilter) <> 0 Then
        sFilter = Left$(sFilter, Len(sFilter) - 5)
        With frm
            .Filter = sFilter
            .FilterOn = True
        End With
    Else
        frm.FilterOn = False
    End If
    Set frm = Nothing
End Sub

The date range code works flawlessly to filter down the form (apologies, I mistakenly said this is to filter a report earlier), however the txtComplaintCategory doesn't work.

I get a 3075 error that states: syntax error (missing operator) in query expression 'and [ComplaintCategory ="' and when I debug it, it highlights the ".filter = sFilter" line

[Micron]

Dim sFilter As String

Set frm = [Forms]![frm_complaints]

If Len(sFilter) <> 0 Then << of course it does. Nothing has been assigned to it.

so sFilter = "" thus IF IsNull...
sFilter = sFilter & " and [ComplaintCategory] = """ & Me.txtComplaintCategory & """"
translates to "" & " and [ComplaintCategory] = """ & Me.txtComplaintCategory & """"

so sFilter now = " and [ComplaintCategory] = """ & Me.txtComplaintCategory & """"

IF IsNUll was true AND Me.txtStartDate is a date then concatenate this
" and [ComplaintCategory] = """ & Me.txtComplaintCategory & """" onto what you already have so then it becomes

[ComplaintNumber] IN (" & sFilter & ") "and [ComplaintCategory] = """ & Me.txtComplaintCategory & """

but since sFilter was " and [ComplaintCategory] = """ & Me.txtComplaintCategory doesn't it become

[ComplaintNumber] IN (" & " and [ComplaintCategory] = """ & Me.txtComplaintCategory " and [ComplaintCategory] = """ & Me.txtComplaintCategory & """"

I dunno - my eyes are starting to spin. Maybe I'm full of it because only one of those IF's could ever be, but it sure doesn't look that way.

Surely you have been told walk through your code and check your variables at least once before?? If not, then I will be the first I guess.


If Len(sFilter) <> 0 Then
Debug.Print sFilter
Stop

[June7]

You are missing & " AND " at the end of ComplaintCategory criteria string. I should have recognized that from initial post but seeing rest of code does help.

What are the complaint categories? Do you have a lookup table for them? Why not save CategoryID instead of descriptive text and use a combobox instead of textbox? Can a complaint be associated with multiple categories?

Could use same code to build filter criteria for a report.

[CJ_London]

Also the ' and ' at the start of that line should not be there (needs to be at the end as June says) - false assumption on my part that it would only be added if that particular criteria was required.