Hi,
I got a little time and so I ran this quick mock-up to create a SQL string that is syntactically correct.
I have hard-coded values into variables because I didn't have time to create the Form that you have and use the Values from controls on the Form.
Still - the SQL comes out correct and I hope you can use this example to solve your problem.
Code:
Function CreateSQLSearchString()
Dim strSQLHead As String
Dim strSQLWhere As String
Dim strSQLOrderBy As String
Dim strSQL As String
Dim strJoin As String
'********** My Test Code
Dim strZip, strCountry, strService As String
Dim BoolLike As Boolean
strZip = "12345"
strCountry = "Tanzania"
strService = "Child Development programs"
BoolLike = False
'********** End - My Test Code
strJoin = " AND "
strSQLHead = "SELECT * FROM QryResourceContactsColumn "
'********** My Test Code
If Len(strZip & vbNullString) Then
If (BoolLike) Then
strSQLWhere = "WHERE Zipcode Like '" & Chr$(39) & "*" & strZip & "*" & Chr$(34)
Else
strSQLWhere = "WHERE Zipcode = " & Chr$(34) & strZip & Chr$(34)
End If
End If
If Len(strCountry & vbNullString) Then
If (BoolLike) Then
strSQLWhere = strSQLWhere & " AND CountyName Like " & Chr$(34) & "*" & strCountry & "*" & Chr$(34)
Else
strSQLWhere = strSQLWhere & " AND CountyName = " & Chr$(34) & strCountry & Chr$(34)
End If
End If
If Len(strService & vbNullString) Then
If (BoolLike) Then
strSQLWhere = strSQLWhere & " AND TypeOfService Like " & Chr$(34) & "*" & strService & "*" & Chr$(34)
Else
strSQLWhere = strSQLWhere & " AND TypeOfService = " & Chr$(34) & strService & Chr$(34)
End If
End If
'********** End - My Test Code
strSQL = strSQLHead & strSQLWhere & strSQLOrderBy
MsgBox strSQL
End Function
I have attached jpg's of the two sql strings in my MsgBox.
I hope this helps.