Where Clause Not Working - Please Help

[asafblasberg]

Look at the code below. I am creating a search form with textboxes. Then, the query gets created, and is added to SSQL. I'm having a problem with the AND -- where to put the AND command eg. select * from table where this = that AND ... etc. etc. --

Thanks!
AB

'Create Where Clause Based on Entries from User
Dim sSQL As String
sSQL = "Select * from quotes where "

If Me!SalesPerson <> "" Then
sSQL = sSQL & " SalesPersonName = '" & Me!SalesPerson & "'"
End If

If Me!FirstName <> "" Then
sSQL = sSQL & " FirstName LIKE '%" & Me!FirstName & "%'"
End If

If Me!LastName <> "" Then
sSQL = sSQL & "LastName LIKE '%" & Me!LastName & "%'"
End If

If Me!Company <> "" Then
sSQL = sSQL & " Company LIKE '%" & Me!Company & "%'"
End If

If Me!Phone <> "" Then
sSQL = sSQL & " Phone = '" & Me!Phone & "'"
End If

If Me!City <> "" Then
sSQL = sSQL & " City LIKE '%" & Me!City & "%'"
End If

If Me!State <> "" Then
sSQL = sSQL & " State = '%" & Me!State & "%'"
End If

If Me!Zip <> "" Then
sSQL = sSQL & " Zip = '%" & Me!Zip & "%'"
End If

If Me!Email <> "" Then
sSQL = sSQL & " Email = '" & Me!Email & "'"
End If

Me!SQLString = sSQL

 

[Trendsetter]

This is an extract from a form that I have in the field doing what you are trying for...

"[SQL]"
If Not IsNull(Me.txtPostcode) Then
str5 = ("PostCode Like " & "'" & "*" & Me.txtPostcode & "*" & "'")
If Flg1 Or Flg2 Or Flg3 Or Flg4 Then
strSQL = strSQL & " And " & str5
Flg5 = True
Else
strSQL = strSQL & " Where " & str5
Flg5 = True
End If
Else
Flg5 = False
End If
"[/SQL]"

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[FancyPrairie]

Try something like this:

Dim sSQL As String
Dim strWhere as String

sSQL = "Select * from quotes "

strWhere = " Where "

If Me!SalesPerson <> "" Then
sSQL = sSQL & strWhere & " SalesPersonName = '" & Me!SalesPerson & "'"
strWhere = " AND "
End If

If Me!FirstName <> "" Then
sSQL = sSQL & strWhere & " FirstName LIKE '%" & Me!FirstName & "%'"
strWhere = " AND "
End If

If Me!LastName <> "" Then
sSQL = sSQL & strWhere & "LastName LIKE '%" & Me!LastName & "%'"
strWhere = " AND "
End If

If Me!Company <> "" Then
sSQL = sSQL & strWhere & " Company LIKE '%" & Me!Company & "%'"
strWhere = " AND "
End If

If Me!Phone <> "" Then
sSQL = sSQL & strWhere & " Phone = '" & Me!Phone & "'"
strWhere = " AND "
End If

If Me!City <> "" Then
sSQL = sSQL & strWhere & " City LIKE '%" & Me!City & "%'"
strWhere = " AND "
End If

If Me!State <> "" Then
sSQL = sSQL & strWhere & " State = '%" & Me!State & "%'"
strWhere = " AND "
End If

If Me!Zip <> "" Then
sSQL = sSQL & strWhere & " Zip = '%" & Me!Zip & "%'"
strWhere = " AND "
End If

If Me!Email <> "" Then
sSQL = sSQL & strWhere & " Email = '" & Me!Email & "'"
strWhere = " AND "
End If

Me!SQLString = sSQL

 

[Zion7]

asafblasberg,
As another option, to what Trendsetter showed...

Dim sSQL As String, vOptions As Variant, sType As String, sType2 As String, x As Integer

vOptions = Array("SalesPersonName", "FirstName", "LastName", "Company", "Phone", "City", "State", "Zip", "Email")

sSQL = "SELECT * FROM Quotes WHERE "

For x = 0 To UBound(vOptions)
  Select Case vOptions(x)
     Case "SalesPersonName", "Phone", "State", "Zip"
         sType = " = '": sType2 = "' AND "
     Case Else
         sType = " Like '%": sType2 = "%' AND "
   End Select

     If Me(vOptions(x)) <> "" Then
        sSQL = sSQL & vOptions(x) & sType & Me(vOptions(x)) & sType2
     End If
Next x

Me!SQLString = Left(sSQL, Len(sSQL) - 5)

I took the liberty in making the code, a little more condensed. Worked fine for me, hope the same for you!

Either way, Good Luck!