Solved Syntex Error

[Momma]

I'm getting a run-time error on this code and I think I'm missing a bracket. Can someone please help me?
Thank you!!

Sub Search()
    Dim tempCriteriaForReport As TempVar
    Dim strSearch, strCriteria, strGender, strColour As String
    Dim task As String
    Dim varItem As Variant
    If Not IsNull(Me.txtColour) Then
        strColour = "([colour] like ""*" & Me.txtColour & "*"") or ([notes] like ""*" & Me.txtColour & "*"")"
        strCriteria = strColour
    End If
    For Each varItem In Me!ListGender.ItemsSelected
        strGender = strGender & "[GenderID] = " & Me!ListGender.ItemData(varItem) & Chr(34) & "Or"
    Next varItem
    If Len(strGender) > 0 Then
        strGender = Left(strGender, Len(strGender) - 2)
        strCriteria = strCriteria & " And (" & strGender & ")"
    End If

    task = "select * from [qrywaitinglist] where (" & strCriteria & ")"
    Me.FilterOn = True
    DoCmd.ApplyFilter task
    TempVars!tempCriteriaForReport = task '' set temp string criteria for report preview
    Me.txtTotal = FindRecordCount(task)
End Sub

 

[theDBguy]

In the error message, looks like there's an extra double quote after GenderID=1 and also after GenderID=2.

 

[June7]

Debug.Print the constructed SQL string. Copy/paste to query object and see if it will work.

You are mixing OR and AND operators. Parentheses will be critical.

 

[The_Doc_Man]

Change the quoting. Where you have doubled double-quotes, I put a single apostrophe.

        strColour = "([colour] like '*" & Me.txtColour & "*') or ([notes] like '*" & Me.txtColour & "*')"
        ...
        strGender = strGender & "[GenderID] = " & Me!ListGender.ItemData(varItem) & Chr(34) & " Or"

There is also an unbalanced apostrophe ( Chr(34) ) before the "Or" of strGender.

Unbalanced quoting frequently messes up bracketing that is partly in and partly outside of quotes.

 

[plog]

There's no space after the OR in your gender block.

You need a set of parentheses around your entire color criteria like you do with your gender criteria.

If no criteria is used your SQL is going to be bad. Either initialize strCriteris to "(1=1)". Or test it and only use it if len()>1.

strSearch is never used.

Comment your code.

 

[The_Doc_Man]

plog, the "OR" is removed a couple of lines later. Why it was there in the first place isn't clear... but by the time the gender string gets used, that is no longer an issue.

 

[plog]

I believe it is an issue. Check out his runtime error pic. No space around that OR

 

[Josef P.]

I think there are not only syntax errors but also logic errors.

If Not IsNull(Me.txtColour) Then
        strColour = "([colour] like ""*" & Me.txtColour & "*"") or ([notes] like ""*" & Me.txtColour & "*"")"
        'strCriteria = strColour  ' <-- brackets are missing because of "Or
        strCriteria = "(" & strColour & ")"  '  <-- Is txtColour always filled?
                                             ' Otherwise " And " & ... and truncating the " And " at the end of the filter composition.
    End If
    For Each varItem In Me!ListGender.ItemsSelected
        'strGender = strGender & "[GenderID] = " & Me!ListGender.ItemData(varItem) & Chr(34) & "Or" ' is numerical?  => Chr(34) is wrong;  " Or "
        strGender = strGender & "[GenderID] = " & Me!ListGender.ItemData(varItem) & " Or "
    Next varItem
    If Len(strGender) > 0 Then
        strGender = Left(strGender, Len(strGender) - 4) ' <--  Len(..) - len(" OR ")
        strCriteria = strCriteria & " And (" & strGender & ")"
    End If

I find it easier to read if you write " Or " and " And " at the beginning:

If Not IsNull(Me.txtColour) Then
        strColour = "([colour] like ""*" & Me.txtColour & "*"") or ([notes] like ""*" & Me.txtColour & "*"")"
        strCriteria = " And (" & strColour & ")"
    End If
    For Each varItem In Me!ListGender.ItemsSelected
        strGender = strGender & " Or [GenderID] = " & Me!ListGender.ItemData(varItem)
    Next varItem
    If Len(strGender) > 0 Then
        strGender = Mid(strGender, len(" OR ")+ 1)
        strCriteria = strCriteria & " And (" & strGender & ")"
    End If

if len(strCriteria) > 0 then
     strCriteria = mid(strCriteria, len(" And ") +1 )
end if

BTW: The for-each loop construction lends itself to a general reusable function that returns the final filter expression.

strGender = BuildNumericCriteriaFromSelectedListboxItems(me.ListGender, " Or ", "GenderID", "=")
' .. find a better name ;)

 

[Momma]

Thank you for everyone's suggestions. I got the code from a search sample database and wasn't sure what the If statement in row 70 was doing. I took it out and it seems like it was part of the problem.
This is what worked at the end
Immediate window:
([colour] like '*choc*' OR [notes] like '*choc*' OR [colour] like '*any*') And ([GenderID] = 1 OR [genderid] = 3)

Sub Search()
          Dim tempCriteriaForReport As TempVar
          Dim strSearch, strCriteria, strGender, strColour As String
          Dim task As String
          Dim varItem As Variant
10        If Not IsNull(Me.txtColour) Then
20            strColour = "([colour] like '*" & Me.txtColour & "*' OR [notes] like '*" & Me.txtColour & "*' OR [colour] like '*any*')"
30            strCriteria = strColour
40        End If
          
50        For Each varItem In Me!ListGender.ItemsSelected
60            strGender = strGender & "[GenderID] = " & Me!ListGender.ItemData(varItem) & " OR "
70        Next varItem
          'If Len(strGender) > 0 Then
              'strGender = Left(strGender, Len(strGender))
80            strGender = strGender & "[genderid] = 3"
90            strCriteria = strCriteria & " And (" & strGender & ")"
          'End If
          
100       Debug.Print strCriteria
          
110       task = "select * from [qrywaitinglist] where (" & strCriteria & ")"
120       Me.FilterOn = True
130       DoCmd.ApplyFilter task
140       TempVars!tempCriteriaForReport = task '' set temp string criteria for report preview
150       Me.txtTotal = FindRecordCount(task)
End Sub

 

[plog]

Could the user make that sub execute if no txtColour or gender is input?

 

[Momma]

Could the user make that sub execute if no txtColour or gender is input?

 

[Momma]

Yes, I can choose colour only or gender only and it works.

 

[Josef P.]

If Me.txtColour is Null, the code in #9 will not work.
The result when nothing is selected in ListGender is the expected?