Syntax error (missing operator)

[josephbupe]

Hi,

Below is part of the code I am using to query database on a form. However, the combination of the two blocks of code (i.e. ' Check for Sex/Gender in multiselect list and ' Check for Rank in multiselect list) below is not working. Instead I get Syntax error (missing operator) in query expresiion '[Sex] In("Female")[Rank] In("Femal)'. But when I remove any one of the two the query works without an error.

    '[COLOR=SeaGreen] Check for Sex/Gender in multiselect list[/COLOR]
    
    For Each varItem In Me.lstGender.ItemsSelected
        Answer = Answer & Me.lstGender.ItemData(varItem) & ""","""
    Next varItem

    If Len(Answer & vbNullString) <> 0 Then
        Answer = Left(Answer, Len(Answer) - 2)
    End If
    
    If Len(Answer & vbNullString) <> 0 Then
        varWhere = varWhere & "[Sex] In(""" & Answer & ")"
    End If

 ' [COLOR=SeaGreen]Check for Rank in multiselect list[/COLOR]
    
    For Each varItem In Me.lstRank.ItemsSelected
        Answer = Answer & Me.lstRank.ItemData(varItem) & ""","""
    Next varItem

    If Len(Answer & vbNullString) <> 0 Then
        Answer = Left(Answer, Len(Answer) - 2)
    End If
    
    If Len(Answer & vbNullString) <> 0 Then
        varWhere = varWhere & "[Rank] In(""" & Answer & ")"
    End If

    ' [COLOR=SeaGreen]Check if there is a filter to return...[/COLOR]
    If IsNull(varWhere) Then
        varWhere = ""
    Else
        varWhere = "WHERE " & varWhere
        
        ' [COLOR=SeaGreen]strip off last "AND" in the filter[/COLOR]
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
    End If

    BuildFilter = varWhere
    
End Function

How do I resolve this?

Joseph

 

[pr2-eugin]

Hello Joseph, I am not sure, but at a quick glance I found the error says In("Femal)'. I think the Left(answer,2) is cutting off e" so see what is going on there.. I will (if time permits) will look into your code in detail after a while..

 

[josephbupe]

I have been looking through the codeand observed the same but did not know how to fix it. I will appreciate your help.

Thank you Paul.

Joseph

 

[pr2-eugin]

Hello Joseph, Just some quick question,
1. Why have you decided to go for Multi select Listbox for Gender, why not OptionBox/Checkbox?
2. What is Rank? Why is that also a MultiSelect Listbox?
3. Why do you have """,""" ?

Suggestion, Try Debug.Print after every End If and see if you get the answer that you wish to see.. Also could you post a small example? What data you give the code, what output you would like to see for varWhere?

 

[josephbupe]

Hi once again Eugin,

By the way I am just trying to adopt the code and expand on it by including more controls on the form.

1. Why have you decided to go for Multi select Listbox for Gender, why not OptionBox/Checkbox?

I need the multi select listbox to be able to select one or more than one entries.

2. What is Rank? Why is that also a MultiSelect Listbox?

Rank is for ranks like in the military.

3. Why do you have """,""" ?

Not quite sure since it appears in the original working sample I am trying to adopt.

Please, have a look through the attached sample for further assistance.

Joseph

 

[pr2-eugin]

I have made some changes, It filters now.. Changes I made were,
1. You were using the same variable Answer to generate the Sex and Rank, however it would be easier if you had used two different variables.. So I created another variable rankAnswer.
2. You did not use the AND keyword for criteria Sex, Rank. So I added that.
3. Left function had to eliminate the last comma as well. So I replaced 1 to 2.

Check the attachment. Hope that helps. You might want to delete the Debug.Print statement in the function.

 

[josephbupe]

Thank you Eugin.

I got a similar error when combining Rank with another field e.g NRC, Family Name etc.

Stay well.

Joseph