SteveL13
asked on
Why a search form isn't working
I have an unbound form that has 5 fields on it. The purpose of the for is to allow the user to enter a string of characters in any of the fields and have a query then feed the information to a 2nd form that uses the query as its record source. I though it was working yesterday but it isn't. I have copy/pasted the SQL of the query designer below.
The problem is that if for example, a record doesn't have anything in the "Company" field, the query won't return records if the user types "John" in the fist name field.
Here is the SQL:
SELECT Contacts.Lname, Contacts.Fname, Contacts.Company, Contacts.Phone1, Contacts.Email, Contacts.IDEE, Contacts.Date, Contacts.MI, Contacts.CType, Contacts.Address, Contacts.Suite, Contacts.City, Contacts.State, Contacts.Zip, Contacts.Phone2, Contacts.HomePhone, Contacts.Fax, Contacts.MailDate, Contacts.MailItem, Contacts.Comments, Contacts.AccountRep, Contacts.LastModUser, Contacts.LastModDate, Contacts.EmailList, Contacts.MailingLabel, Contacts.FaxList, Contacts.[BAWB Member], Contacts.[BAWB Membership Date], Contacts.[BAWB Expiration Date], Contacts.[BAWB Member Since]
FROM Contacts
WHERE (((Contacts.Lname) Like "*" & [Forms]![frmSearchContacts ]![txtLast Name] & "*") AND ((Contacts.Fname) Like "*" & [Forms]![frmSearchContacts ]![txtFirs tName] & "*") AND ((Contacts.Company) Like "*" & [Forms]![frmSearchContacts ]![txtComp any] & "*") AND ((Contacts.Phone1) Like "*" & [Forms]![frmSearchContacts ]![txtPhon e1] & "*") AND ((Contacts.Email) Like "*" & [Forms]![frmSearchContacts ]![txtEmai l] & "*"))
ORDER BY Contacts.Lname, Contacts.Fname, Contacts.Company;
The problem is that if for example, a record doesn't have anything in the "Company" field, the query won't return records if the user types "John" in the fist name field.
Here is the SQL:
SELECT Contacts.Lname, Contacts.Fname, Contacts.Company, Contacts.Phone1, Contacts.Email, Contacts.IDEE, Contacts.Date, Contacts.MI, Contacts.CType, Contacts.Address, Contacts.Suite, Contacts.City, Contacts.State, Contacts.Zip, Contacts.Phone2, Contacts.HomePhone, Contacts.Fax, Contacts.MailDate, Contacts.MailItem, Contacts.Comments, Contacts.AccountRep, Contacts.LastModUser, Contacts.LastModDate, Contacts.EmailList, Contacts.MailingLabel, Contacts.FaxList, Contacts.[BAWB Member], Contacts.[BAWB Membership Date], Contacts.[BAWB Expiration Date], Contacts.[BAWB Member Since]
FROM Contacts
WHERE (((Contacts.Lname) Like "*" & [Forms]![frmSearchContacts
ORDER BY Contacts.Lname, Contacts.Fname, Contacts.Company;
ASKER
Didn't work. Please see attached test file.
On the form enter "doe" in the last name field. The query will return a record as it should.
Then enter "meade" in the last name field. The query will not return a record even though there is a record with "meade" in the Lname field.
????????
Test.accdb
On the form enter "doe" in the last name field. The query will return a record as it should.
Then enter "meade" in the last name field. The query will not return a record even though there is a record with "meade" in the Lname field.
????????
Test.accdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That worked. Now if I could just get the form to order by Lname
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Almost. But if I don't put anything in the search form fields, which would return all records, I get another syntax error:
"Syntax Error in WHERE clause"
"Syntax Error in WHERE clause"
post the codes you are using.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks to both of you I was able to put this together and it works great!
Dim s As String
If Nz(Me.txtLastName, "") <> "" Then
's = s & " LName Like *" & Me.txtLastName & "*"
s = s & " LName Like '*" & Me.txtLastName & "*'"
End If
If Nz(Me.txtFirstName, "") <> "" Then
If Len(s) > 0 Then
s = s & " AND "
End If
s = s & " FName Like '*" & Me.txtFirstName & "*'"
End If
If Nz(Me.txtCompany, "") <> "" Then
If Len(s) > 0 Then
s = s & " AND "
End If
s = s & " Company Like '*" & Me.txtCompany & "*'"
End If
If Nz(Me.txtEmail, "") <> "" Then
If Len(s) > 0 Then
s = s & " AND "
End If
s = s & " Email Like '*" & Me.txtEmail & "*'"
End If
If Nz(Me.txtPhone1, "") <> "" Then
If Len(s) > 0 Then
s = s & " AND "
End If
s = s & " Phone1 Like '*" & Me.txtPhone1 & "*'"
End If
If Len(s) > 0 Then
s = "SELECT * FROM Contacts WHERE " & s & " Order by LName"
Else
s = "SELECT * FROM Contacts" & " Order by LName"
End If
DoCmd.OpenForm "frmContactsExisting", acNormal, , , acFormEdit, acWindowNormal
SELECT Contacts.Lname, Contacts.Fname, Contacts.Company, Contacts.Phone1, Contacts.Email, Contacts.IDEE, Contacts.Date, Contacts.MI, Contacts.CType, Contacts.Address, Contacts.Suite, Contacts.City, Contacts.State, Contacts.Zip, Contacts.Phone2, Contacts.HomePhone, Contacts.Fax, Contacts.MailDate, Contacts.MailItem, Contacts.Comments, Contacts.AccountRep, Contacts.LastModUser, Contacts.LastModDate, Contacts.EmailList, Contacts.MailingLabel, Contacts.FaxList, Contacts.[BAWB Member], Contacts.[BAWB Membership Date], Contacts.[BAWB Expiration Date], Contacts.[BAWB Member Since]
FROM Contacts
WHERE (((Contacts.Lname) Like "*" & [Forms]![frmSearchContacts
ORDER BY Contacts.Lname, Contacts.Fname, Contacts.Company;