Search Form with multiple criteria

[Pharmer Rob]

I am new at this so bear with me. I have created a database to track patient visits. I want to go from my switchboard to a search form that can search records by a combination of Last Name, First Name and D.O.B. I have made one that will search by one criteria not using VBA. Keep in mind I'm a beginner. Thanks in advance.

 

[jtownraven]

try basing your form on a querry that uses [ ] as the criteria. for example, if you put [Enter Last Name] as the criteria for the field, a dialog box will pop up asking you to enter the last name when you run the querry or open the form. if you do this for multiple fields, it will ask for as many as you define in the querry. I don't know how to do this and just make one work at a time though when you fill in multiple criteria. if anyone else knows, it would help me too!

 

[Pharmer Rob]

I have used parameter queries in this way. But I wanted to be able to enter any combo on my search form, hit a search button and retrieve the records that meet the criteria in a list box or something like this. Thanks for the rapid response. I've looked in several books. I don't see this explained anywhere. I have worked on databases that could do this.

 

[Dave Eyley]

For 3 criteria I would look at the database attached below. I got it from the forum; it was posted by Candace Tripp and is one way to use Cascading combos to filter information.

If this isn't what you're looking for then I have a multi-criteria search routine that I found and modified, which consists of a form with unbound fields that accept data, verify the inputs, create a search string and apply it to a query to produce results in a subform. I will have to cut it out of the database to post but I will do it if necessary.

Happy Weekend all.

Dave E

 

[jtownraven]

maybe this

well, i'm new, so i don't understand this much either. i did get this file off of another forum, but i couldn't get it to work with my tables. maybe it will help, but it was too much for me, for now.

 

[jtownraven]

maybe this

well, i'm new, so i don't understand this. i did get this file off of another forum, but i couldn't get it to work with my tables. maybe it will help, but it was too much for me, for now. i think i even messed up sending my last post.

 

[Pharmer Rob]

Thank you so much for the help. What I am looking for is what Dave is describing in the search criteria routine. I want the user to be able to enter a last name and/or first name and/or d.o.b. and filter the recordset. I have attached the search form that I want to use to filter the records. Again, thank you so much.

 

[Dave Eyley]

Pharmer Rob,

I've knocked this together tonight for you to look at. It will answer a lot of your questions about multi-criteria searches.
I'm sure it's not the only way - as others will no doubt testify - but it will give you a start.

It is by no means a complete database, only an example for you to help you develop your own application.


I hope you enjoy dissecting it.

Good Luck

 

[Dave Eyley]

Pharmer Rob,

I've knocked this together tonight for you to look at. It will answer a lot of your questions about multi-criteria searches.
I'm sure it's not the only way - as others will no doubt testify - but it will give you a start.

It is by no means a complete database, only an example for you to help you develop your own application.


I hope you enjoy dissecting it.

Good Luck

Dave Eyley

 

[Pharmer Rob]

Thanks so much. I've been agonizing over this for a while.

 

[mbentle]

WOW! I'm a n00b and as every good n00b should, I've been jumping around these forums finding various answers and a wealth of information before asking stupid questions (I already have asked a few).

What I'd love to do more than anything is combine the multicriteriasearch and findrecord db's into something else. Is there any way to select the search types from a combo box (i.e. last name, first name, etc), enter in the criteria below it in a text box (i.e. Smith, John, etc) and then display the results of the query below that in a sub form? Then, if we could make that subform selectible and display the individual account information for the result highlighted in another subform (hence two subforms on one form), I think it would accomplish what I'm trying to set-up. My problems lie in setting up that combo search function and creating the linked sub forms that are working around selectible query results.

 

[Dave Eyley]

mbentle,

If you can understand the multisearchcriteria example in this thread then adding another subform to show the full details of one of the filtered records should be relatively easy.

Start by looking at the way the existing example selects individual records to view. (The area where it says click on individual record to view deatils) Then think about using this method to pull off the ID value and use that as the link to the subform where the individual record is to be viewed.

So, in essence, the user follows the methods as used on the existing example to filter the records which are then displayed in the first subform (as shown).

Then the user clicks on an individual record's ID and instead of the action opening a new form with the record whose ID matches, it applies the ID to the filter of a second subform (that you created) to display the details....

The second subform will, of course, be set to display a single record and probably as a form?

Enough to think about?

HTH

Dave

 

[Icehousman2]

Dave,

Thanks for the example it works great for me except one thing. What if i have to use the "or" operator. For example, i am using this on a customer database, where the user might want to limit the results by multiple offices. Underneath I have posted the code that i'm using. Now when I limit criteria in all three of the fields i'm left with the search string being something like this: searchstr = exp(1) or exp(2) and exp(3). This doesn't work, becuase the 3rd expression is only applied in conjuction with the 2nd expression, while all records that meet the 1st epression are permitted, even if they fail the 3rd expression. Is there a way around this, using the example you came up with. I really like it and it is easy to use, so i would hate to have to figure out another way. Thanks.

Dim Searchstr As String

If Not IsNothing(Combo10) Then
If IsNothing(Searchstr) Then
Searchstr = "[officeid]= " & Me!Combo10
Else
Searchstr = Searchstr & " [officeid]= " & Chr$(34) & Me!Combo10 & Chr$(34)
End If
End If

If Not IsNothing(Combo14) Then
If IsNothing(Searchstr) Then
Searchstr = "[officeid]= " & Me!Combo14
Else
Searchstr = Searchstr & " or [officeid]= " & Me!Combo14
End If
End If

If Not IsNothing(Check6) Then
If IsNothing(Searchstr) Then
Searchstr = "[techtip]= " & Me!Check6
Else
Searchstr = Searchstr & " And [techtip]= " & Me!Check6
End If
End If

Me!Text27 = Searchstr
Me![subFilter].Form.Filter = Searchstr
Me![subFilter].Form.FilterOn = True

 

[Dave Eyley]

Still working on this one, eh?

Well I think your problem is just one of brackets. Using brackets will determine which parts of the logical search string will be resolved first.

So,

Exp1 or Exp2 and Exp3 - will get your result...

While,

Exp1 or (Exp2 and Exp3) - will achieve your aim, I think.

You might like to look at Xor as an option too. The Help files will give you info on this...

HTH

Dave E

 

[smercer]

This will help you with your Parramiter queries and how to make them look at values in text boxes in forms too.

 

[Icehousman2]

Thanks for your responses,

I got that problem fixed. But now have another problem. As you can see in my code, i use a check box for one of the criteria. Well, with this code if the check box value is zero. OR not checked, then the isnothing function returns, nothing and the following code is not run. Now the check box is in triple state mode, so that if it is null, i don't want it to be included in the criteria, but if it is blank, or unchecked, then i do want it inlcuded. In other words, even though the value is zero I still want the code in the expression to run. So, first i just stopped using the isnothing function but then the check box is included in the criteria everytime. I don't want this. So then i changed the isnothing function in the module to include zero values. But that didn't work either. Not sure why on that one. Do you have any ideas on how to achieve this?

 

[Dave Eyley]

I think this will work...

If Not IsNull(Me!Checkbox) Then
If IsNothing(Searchstr) Then
Searchstr = "[Checkbox]= " & Me!Checkbox
Else
Searchstr = Searchstr & " AND [Checkbox]= " & Me!Checkbox
End If
End If

It tests for a Null Checkbox and if it's Null omits the Checkbox criteria

This is one of those times when the IsNull() test is better than the IsNothing()

HTH

Dave Eyley

 

[Icehousman2]

Thanks Dave,

That worked perfectly.

 

[Dave Eyley]

It's always good when it comes together, don't you think?

The trouble I've had with Nulls and Nothings!!!

Dave Eyley