Multiple Criteria From Text Boxes Issue

[kdenney]

I have a form that has these fields in it:

First Name
Last Name
Phone Number
Email Address
Home Address
State
Zip Code

These are on a Search Form that I enter corresponding search criteria into and I have it driving a query. Then it brings up the results of that query in another form (tabular). The problem is that I am having trouble with my criteria not working properly. Case and point:

If I have a First Name but don't have anything else I want it to display everything that still has that First Name. I also want that same mentality to work for all the fields at the same time. For instance if I knew what last name and state I was looking for but didn't know anything else I would want to bring up everyone with a certain last name living in a certain state. I also would like it to work for three criteria etc. or more on every field. Is this possible? Thank you and how could it be accomplished if it is?

 

[alansidman]

Look at this tutorial. I think it should solve your issues.

http://www.datapigtechnologies.com/flashfiles/searchform.html

Alan

 

[kdenney]

This seems to only work if all the fields are carried out. I have done everything the video explains... any ideas?

 

[Avatar]

This is a little crude and could be streamlined a fair bit, but; how about somthing like this:

 With Me
  If Me.txt_FName <> "" Then: bFName = True
  If Me.txt_LName <> "" Then: bLName = True
  If Me.txt_TelNo <> "" Then: bTelNo = True
  If Me.txt_eMail <> "" Then: beMail = True
  If Me.txt_HomeAdd <> "" Then: bHomeAdd = True
  If Me.txt_State <> "" Then: bState = True
  If Me.txt_Zip <> "" Then: bZip = True
 
  MySQL = _
   "SELECT FirstName, LastName, PhoneNumber, eMailAdd, HomeAdd, State, ZipCode " & _
   "FROM tbl_name "
 
  If bFName Or bLName Or bTelNo Or beMail Or bHomeAdd Or bState Or bZip Then
   MySQL = MySQL & _
    "WHERE ("
   If bFName Then
    MySQL = MySQL & "([FirstName] = '" & Me.txt_FName & "')"
    If (bLName + bTelNo + beMail + bHomeAdd + bState + bZip) <> 0 Then MySQL = MySQL & " AND "
   End If
   If bLName Then
    MySQL = MySQL & "([LastName] = '" & Me.txt_LName & "')"
    If (bTelNo + beMail + bHomeAdd + bState + bZip) <> 0 Then MySQL = MySQL & " AND "
   End If
   'and so on...
   '...
   '...
   MySQL = MySQL & _
   ")"
  End If
 End With
 MySQL = MySQL & _
  "ORDER BY FirstName;"

 

[kdenney]

Here is an example of what I am trying to do:

First Name Last Name Phone Number Email

Kyle 555-555-5555 kyle@yahoo.com

Where the blank is in the Last Name field I want it to still return that in the query. My default value of my text boxes is "". My query language is
Like [Forms]![Search].[FirstName] & "*"

But it only works if every field is filled out and doesn't help me if I enter a first name but the last name or the phone number etc. is blank. Or if I enter a Last Name but the first name is blank or email is blank it wont return that value... it really defeats my purose. If I enter a First Name I want it to return every value in the table where the First Name is Kyle regardless of whether or not anything else is or is not filled out. Is this possible? What am I missing?

PS I don't know how to do the whole SQL stuff so if we can do this just by setting query criteria that would be great!

 

[alansidman]

Take a look at this from the MS KB web site and see if it helps.

http://support.microsoft.com/kb/209645/en-us

Alan