Mavislee
asked on
Using multiple parameters in a query
Hello experts -
I'm working on a search form in Access 2000 that will allow a user to enter a single criteria or multiple criteria to search on. I have a form with 4 fields. I used the query expression builder to reference these fields in the query grid. However, the query does not return any records unless all 4 criteria are filled in. I looked through the knowlege base and couldn't find anything that wasn't overly complicated for my level of experience. Is there an easy way to get this query to run when there are less than all 4 criteria filled in? Thanks for any help you can give!
I'm working on a search form in Access 2000 that will allow a user to enter a single criteria or multiple criteria to search on. I have a form with 4 fields. I used the query expression builder to reference these fields in the query grid. However, the query does not return any records unless all 4 criteria are filled in. I looked through the knowlege base and couldn't find anything that wasn't overly complicated for my level of experience. Is there an easy way to get this query to run when there are less than all 4 criteria filled in? Thanks for any help you can give!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry it took so long to get back to this. I was actually trying each of your suggestions and couldn't get anything to work so I tried to simplify this whole thing by putting in less criteria. I thought Ryan DeMougin's was the simpliest to implement, but for some reason the wildcard is not working in the iif statement. Instead of four options, I put in beginning and end dates (which works fine) and a single combo box.The query getting criteria from the combobox looks like this:
iif(isNull(Forms![frm_Sear
I get nothing back if cboPatient is null,but I do get the correct record if it's not. Does anyone have any more ideas? If not, I'm going to create 2 different queries and call each one based on the criteria input.
iif(isNull(Forms![frm_Sear
I get nothing back if cboPatient is null,but I do get the correct record if it's not. Does anyone have any more ideas? If not, I'm going to create 2 different queries and call each one based on the criteria input.
ASKER
Since I didn't hear anything back from anyone, I did a workaround. I created separate queries, each dependent on the data inputted. If all criteria is filled in, one query is called. If only some criteria is filled in, another query is called. Arji's solution didn't work because I was trying to limit the number of records returned. GrayL's solution was close, but I had a problem getting it to work. Again, I think it was the * in the expression builder. I also RyanDeMougin's and had the same problem. Still I thank everyone for responding and I'm awarding at least some points to each for your effort. Thanks again!
Example grid:
Field: Field1 Field2 Field3 Field4
Criteria: [Enter search text 1]
Or: [Enter search text 2]
Or: [Enter search text 3]
Or: [Enter search text 4]
Hope this is clear