Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 149
  • Last Modified:

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]![txtLastName] & "*") AND ((Contacts.Fname) Like "*" & [Forms]![frmSearchContacts]![txtFirstName] & "*") AND ((Contacts.Company) Like "*" & [Forms]![frmSearchContacts]![txtCompany] & "*") AND ((Contacts.Phone1) Like "*" & [Forms]![frmSearchContacts]![txtPhone1] & "*") AND ((Contacts.Email) Like "*" & [Forms]![frmSearchContacts]![txtEmail] & "*"))
ORDER BY Contacts.Lname, Contacts.Fname, Contacts.Company;
0
SteveL13
Asked:
SteveL13
  • 5
  • 4
  • 2
4 Solutions
 
Rey Obrero (Capricorn1)Commented:
try this

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]![txtLastName] & "*") AND ((Contacts.Fname) Like "*" & [Forms]![frmSearchContacts]![txtFirstName] & "*") AND ((Contacts.Company) Like "*" & [Forms]![frmSearchContacts]![txtCompany] & "*" OR (Contacts.Company) Is Null) AND ((Contacts.Phone1) Like "*" & [Forms]![frmSearchContacts]![txtPhone1] & "*") AND ((Contacts.Email) Like "*" & [Forms]![frmSearchContacts]![txtEmail] & "*"))
 ORDER BY Contacts.Lname, Contacts.Fname, Contacts.Company;
0
 
SteveL13Author Commented:
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
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If I add a value to the Phone1 field for the Meade row, the query works.

IMO, you're creating a search form incorrectly. Instead of creating a query that references the form, instead build up the full SQL of the query, then show the results on a Datasheet form, or something of that nature.

So the "Run" button would do something like this:

Dim s As String

If Nz(Me.txtLastName, "") <> "" Then
  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

<and so on for other controls>

s = "SELECT * FROM Contacts WHERE " & s

Me.DataSheetSubFormControlName.Form.Recorsource = s
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
SteveL13Author Commented:
I'm getting a syntax error using: (see file attached)syntax error
Dim s As String

 If Nz(Me.txtLastName, "") <> "" Then
   s = s & " LName Like *" & Me.txtLastName & "*"
 End If
0
 
Rey Obrero (Capricorn1)Commented:
If Nz(Me.txtLastName, "") <> "" Then
   s = s & " LName Like '*" & Me.txtLastName & "*'"
 End If
0
 
SteveL13Author Commented:
That worked.   Now if I could just get the form to order by Lname
0
 
Rey Obrero (Capricorn1)Commented:
s = "SELECT * FROM Contacts WHERE " & s & " Order by LName"
0
 
SteveL13Author Commented:
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"
0
 
Rey Obrero (Capricorn1)Commented:
post the codes you are using.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Do this:


If Len(s) > 0 Then
  s = "SELECT * FROM Contacts WHERE " & s
Else
  s = "SELECT * FROM Contacts"
End IF
0
 
SteveL13Author Commented:
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

Open in new window

0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now