Syntax Error - VB code 1

[kklaus]

I'm trying to write some code in VB for an Access Database. I'm writing a procedure that updates the selection available for a Combobox based on selections from 2 other Comboboxes. However, the tricky part for me is the "OR" piece. The user does not have to make selections for both comboboxes and could leave one blank. Therefore, I have the "IsNull" in the "Or" part of the Where Clause.

I'm getting a Syntax error that says "missing operator". I'm sure it has something to do with my punctuation but I'm not sure where. Any help would be great!

Private Sub Combo23_AfterUpdate()
    
    Dim strSQL As String
        
    Combo19 = Null
    
    strSQL = " Select DISTINCT Underwriters.UnderwriterName FROM Underwriters "
    strSQL = strSQL & " WHERE (Underwriters.Office = '" & Combo23 & " ' OR " ' & IsNull(Combo23)) &  " AND "
    strSQL = strSQL & " (Underwriters.Unit = ' " & Combo27 & " ' OR " ' & IsNull(Combo27)) & ' "
    strSQL = strSQL & " ORDER BY Underwriters.UnderwriterName;"
    
    Combo19.RowSource = strSQL

End Sub

Combo27 - is my Business Unit selection (Property, Casualty, etc)
Combo23 - is my Office selection (Roswell, Chicago, etc)
Combo19 - is my Underwriter selection

 

[PHV]

You may try something like this:
strSQL = "SELECT DISTINCT UnderwriterName FROM Underwriters "
strWhere = ""
If Trim(Combo23 & "") <> "" Then
strWhere = strWhere & " AND Office='" & Combo23 & "'"
End If
If Trim(Combo27 & "") <> "" Then
strWhere = strWhere & " AND Unit='" & Combo27 & "'"
End If
If Len(strWhere) > 0 Then
strWhere = "WHERE " & Mid(strWhere, 5)
End If
Combo19.RowSource = strSQL & strWhere & " ORDER BY 1"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kklaus]

That was perfect!

Thanks so much!