Multiple Criteria in Search Form

[LQ]

I am making a search form that has five (unbound) fields. I want users to be able to search on either single or multiple fields. I am able to get this to work with one field using a query that pulls criteria from the unbound combobox. But when I try to specify multiple criteria, or even single criteria from the second combobox on the form, I keep getting every record in the table. Do I need to figure out how to write an SQL statement that looks at each text/combo box and evaluates whether it is null or not? Do I need to use QueryDef? Or is there a simpler way of doing this?

And if I *do* need to use QueryDef or something like that, I would need lots of guidance, since I've never used it before and have just read about it here and in the MS Knowledge Base (and the Knowledge Base says I need "expert coding" skills, which I definitely don't have!)

Thanks for any guidance!

 

[shacket]

You just need to find the correct SQL statement. Here is a suggestion based on a search of 5 fields ([FirstName],[LastName], [Address],[City],[Zip])

Dim mySql As String
Dim And As String
And = "" 'This will be added after the first criterion
mySql = "SELECT * FROM tblYourTable WHERE "
If Nz(Me.txtFirst) <> "" Then
mySql = mySql & "[FirstName] Like *" & Me.txtFirst & "* "
And = "AND "
End If
If Nz(Me.txtLast) <> "" Then
mySql = mySql & And & "[LastName] Like *" & Me.txtLast & "* "
And = "AND "
End If
If Nz(Me.txtAddress) <> "" Then
mySql = mySql & "[Address] Like *" & Me.txtAddress & "* "
And = "AND "
End If
If Nz(Me.txtCity) <> "" Then
mySql = mySql & "[City] Like *" & Me.txtCity & "* "
And = "AND "
End If
If Nz(Me.txtZip) <> "" Then
mySql = mySql & "[Zip] Like *" & Me.txtZip & "* "
End If


You would then apply the sql you created (in the variable mySql) to the RecordSource that you want.

 

[Jack Cowley]

Go to the MS Knowlege Base and open the article on creating QueryDefs. Copy the code and paste it into the button that will give the user their results. Change the field names to the ones you are using. With a little fiddling and patience you will get it to work. If I can do it anyone can do it! If you run into problems post your question and we will give you a hand....

 

[LQ]

Shacket...thanks for the info, but I have one (possibly stupid) question: Where do I put this SQL statement? You say to apply the SQL to the recordsource, but I don't fully understand what you mean.

Jack, I will tinker with querydef, too, and see if I can get it to work.

Thanks!

 

[Liv Manto]

On Open even or on load event

me.recordsource = sqlstring

 

[shacket]

LQ -

you would place the SQL whereever you want to show the records. For example:

Me.ComboBoxName.RowSource = mySQL
or
Forms!YourFormName!RecordSource = mySQL
or
Reports!YourReportName!RecordSource = mySQL
or
Me.ListBoxName.RowSource = mySQL

(per my example above, "mySQL" is the variable that holds the SQL statement that we created)

[This message has been edited by shacket (edited 08-19-2001).]

 

[LQ]

I am feeling stupid and confused this Monday morning!

I tried to put this SQL statement in various places, but nothing seems to work...I would like the users to have one form that is the "search" form where they can specify up to five criteria and then click a button and see a "display" form that has all the fields that are in the table for that particular record. So I am confused on where to put the code -- as the recordsource for the "search" form, or as the recordsource for the "display" form? Or break it up and put it as the source for each individual combobox?

Access also did not like me using the word "and" as a variable. I had to change it to another word, and I don't know if that affected anything.

Any advice will be appreciated...I've already been to the dentist this morning, and I don't need any more pain and suffering!

Thanks

 

[shacket]

You would put the SQL statement as the recordsource for the display form (in other words, display this information). You can do this with the DoCmd.OpenForm command:

DoCmd.OpenForm "myDisplayFormName",,mySQL

That would open the form with the SQL statement you created.

[This message has been edited by shacket (edited 08-21-2001).]

 

[LQ]

I think there are probably multiple things wrong with what I am doing, but one of the errors Access is giving me is: Missing operator in query expression '[Payor] Like *Aetna HMO*'.

Also, I put my sql in the Form Load event for my display form. But I am assuming that the fields I should be referring to are in the search form, since that is where users would put in criteria, right?

If I try to put the statement you suggested in the form's on open event, shacket, I get an error that the variable is not defined.

I am feeling clueless and frustrated, but thanks everyone for your help!

 

[shacket]

WOW! You ARE having problems!

Take a deep breath...*SIGH*...and we'll solve this!

As far as your first error message, that was my mistake. As I look over the code that I wrote, I realize I left something out. The statement that should be CREATED should look like:

[Payor] Like '*Aetna HMO*'

I failed to supply the single quotes. Therefore the code should look like:

mySql = mySql & "[FirstName] Like '*" & Me.txtFirst & "*' "

Note the singles quotes I added in. You would obviously adjust that in every case.

Also, you cannot put the code in the FormLoad event unless you have declared mySQL as a Public Variable, which, if you are using my code, you haven't. That's why you need to use the code I posted earlier:

DoCmd.OpenForm "myDisplayFormName",,mySQL

put this line at the end of the code I gave you. Try those two things and get back to me.

 

[LQ]

shacket, thank you for your patience!!

I tried to follow your advice, but I am still having problems. Here is what I put in the "display" form's On Load event:

Dim mySql As String
'This will be added after the first criterion
mySql = "SELECT * FROM [call log] WHERE "
If Nz(Forms!frmsearchcalllog!cbopayor) <> "" Then
mySql = mySql & "[Payor] Like '*" & Forms!frmsearchcalllog!cbopayor & "*' "
End If
If Nz(Forms!frmsearchcalllog!cboinscode) <> "" Then
mySql = mySql & "[Inscode] Like '*" & Forms!frmsearchcalllog!cboinscode & "*' "

End If
If Nz(Forms!frmsearchcalllog!grpcategory) <> "" Then
mySql = mySql & "[Category] Like '*" & Forms!frmsearchcalllog!grpcategory & "*' "
End If
If Nz(Forms!frmsearchcalllog!txtothersubject) <> "" Then
mySql = mySql & "[othersubject] Like '*" & Forms!frmsearchcalllog!txtothersubject & "*' "
End If
If Nz(Forms!frmsearchcalllog!txtquestion) <> "" Then
mySql = mySql & "[Question] Like '*" & Forms!frmsearchcalllog!txtquestion & "*' "
End If
DoCmd.OpenForm "frmcalllog", , mySql

(note: I could not get Access to accept And as String, and I could not figure out exactly what that was doing anyway, so I commented it out...maybe that's part of the problem)

I could not get this to work at all unless I changed the recordsource of the display form to the table. After I changed that, I was able to search sucessfully on the first field in my search form (cbopayor), but when I try to search on my second field, I get a msg box that tells me to enter a parameter value for inscode.

I think I made a *little* progress, but not much!

Thanks again for all your help!

 

[LQ]

OK, I was tinkering and figured out one of my problems....I had typed the name of the field for insurance code incorrectly. Once I corrected that, I was able to search on that field.

So now my problem is this: I can search on any one of the fields, but it seems like I cannot search on more than one field at a time. If I try to do that, I get all of the records in the table. Is there any way to search on more than one field at a time? Does it have anything to do with that pesky "And" that I commented out?

Thanks!

 

[shacket]

I think we're getting there!

We need to put the "And" string back in there, even if we need to call it something else. So you would have to add:

Dim AddAnd As String 'you could name that anything
AddAnd = ""

Then, for the 1st through 4th If statements (you said you have 5), put the following code after you make the SQL statement (next line before "End If"):

AddAnd = "AND "

Then, for the 2nd through 5th SQL statements, make them look like this:

mySQL = mySQL & AddAnd & "[FirstName] Like '*" & Me.txtFirst & "*' "

Then add the

DoCmd.OpenForm "myDisplayFormName",,mySQL

code at the end. This code (all of it) should be on your SEARCH form (not your display form). You may want to attach it to the OnClick event of a command button to initiate the search.

Let me know. I am determined to get this working for you!!!

[This message has been edited by shacket (edited 08-22-2001).]

 

[LQ]

I'm so excited...this seems to be working now!!!!

And I'm actually understanding the theory behind the variables in the SQL now, so I can use it in other places instead of copying it blindly.

Thanks so much for your time, effort and patience, shacket. I couldn't have done it without ya!

 

[shacket]

About a year ago I was in the same boat. Someone spent a whole bunch of time e-mailing me directly about this (creating SQL statements in VBA to use as RecordSources) and I was doing the same thing. "WOW!!!"

I am glad to help.

 

[noswal]

Hey
This discussion has been of great help for me. I am trying to do something on the same line...
byt after following the instructions it does not give exclusive inofrmation but opens the entire search form.

I am not sure what I wrong am I doing
Please help me
Namita
The code is
Private Sub cmdSearch_Click()
Dim stLinkCriteria As String
Dim AddAnd As String
AddAnd = ""

If Nz(Forms!Search!ctlAnalystID) <> "" Then
stLinkCriteria = stLinkCriteria & AddAnd & "[AnalystID] Like '* " & Forms!Search!ctlAnalystID & "&'"
AddAnd = "AND"
End If

If Nz(Forms!Search!ctlBuildingID) <> "" Then
stLinkCriteria = stLinkCriteria & AddAnd & "[BuildingID] Like '* " & Forms!Search!ctlBuildingID & "&'"
AddAnd = "AND"
End If


If Nz(Forms!Search!ctlClosetRoomNumber) <> "" Then
stLinkCriteria = stLinkCriteria & AddAnd & "[ClosetRoomNumber] Like '* " & Forms!Search!ctlClosetRoomNumber & "&'"
AddAnd = "AND"
End If


If Nz(Forms!Search!ctlEquipmentType) <> "" Then
stLinkCriteria = stLinkCriteria & AddAnd & "[EquipmentType] Like '* " & Forms!Search!ctlEquipmentType & "&'"
AddAnd = "AND"
End If


If Nz(Forms!Search!ctlWireType) <> "" Then
stLinkCriteria = stLinkCriteria & AddAnd & "[WireType] Like '* " & Forms!Search!ctlWireType & "&'"
AddAnd = "AND"
End If


If Nz(Forms!Search!ctlCircuitRoomNumber) <> "" Then
stLinkCriteria = stLinkCriteria & AddAnd & "[CircuitRoomNumber] Like '* " & Forms!Search!ctlCircuitRoomNumber & "&'"
AddAnd = "AND"
End If

If Nz(Forms!Search!ctlCircuitID) <> "" Then
stLinkCriteria = stLinkCriteria & AddAnd & "[CircuitID] Like '* " & Forms!Search!ctlCircuitID & "&'"
AddAnd = "AND"
End If

If Nz(Forms!Search!ctlLineOrderNumber) <> "" Then
stLinkCriteria = stLinkCriteria & AddAnd & "[LineOrderNumber] Like '* " & Forms!Search!ctlLineOrderNumber & "&'"
AddAnd = "AND"
End If

DoCmd.OpenForm "Information", , stLinkCriteria
End Sub

 

[LQ]

Have you tried to put in a breakpoint at the end (right before you open your form) and then look in the debug window? That way you can check the value of your variable. I had to do that a lot when I was creating this form!

Also, I noticed that you have an ampersand, as in
"[CircuitID] Like '* " & Forms!Search!ctlCircuitID & "&'"
on the end of each line...I used an asterisk at the end.



[This message has been edited by LQ (edited 11-28-2001).]

 

[jwindon]

Shacket & LQ:

Thanks for sharing your discussion here on the forum (versus private emails).

I am one to have benefited from it.

(Only took me two hours!) :-(

Still, got it to work. Now just to study it so I know it well enough to reproduce on a whim.

Thanks again.

JWindon

 

[raindrop3]

Check out this topic:
http://www.access-programmers.co.uk/ubb/Forum1/HTML/005912.html

Albert

 

[jwindon]

Thanks raindrop, I will be studying your code as well. So many talented people here!