Parameter Query

cres1121
cres1121 used Ask the Experts™
on
I have Queries that we use all the time.  I would like to expand the parameter query that is built in the criteria that are  "Like "*" & [Enter Serial Number]."  

We are getting so big now we would like to not run it for one serial number but for a bunch of them.  The users would actually like to cut and paste the serial numbers from excel and place it in the prompt.  

HELP!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Just a side note ...
When you put a wild card in front of an argument ... Access will *not* be able to use an Index on that column - if one exists.
Of course, this may or may not be a big deal in your case ... depending on the number of records.
So ..

Like "abc" & "*"  is OK
Like "*" & "abc" & "*"  is not ok in this regard ... ie this is a Non Sargable expression.

Author

Commented:
Thank you ..
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
So ...I'm not sure what you are asking here ' expand' ?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
I want to be able to add more than one parameter...  to the or criteria not the and?  How do I let the user put in serial number 1 thru 15 listed individually and return back the report that has just those numbers listed.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Can you post a screen shot of the query in the query designer as it is now - show the criteria as it is currently ?
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Although not recommended, you can accomplish this with syntax similar to:

WHERE "," & [yourField] & ","  IN "," & [Forms]![FormName].TxtParameters & ","

The reason I added the commas before/after [yourField] and before/after the reference to the control on your form is that it allows you to explicitly separate your values, so that every value is a comma preceding and following it in you control, and that every value in the table has a comma preceding and following it.

Personally, I think you are better off parsing the values in the textbox and developing a criteria string dynamically, something like:

Dim Param() as string
Dim varCriteria as variant, intLoop as integer

varCriteria = NULL
Param = Split([Forms]![yourForm].txtParameters)
for intLoop = lbound(Param) to ubound(Param)
    varCriteria = (varCriteria + " OR ") & "([yourField] = " & Param(intLoop) & ")"
Next

currentdb.querydefs("yourQuery").SQL = "SELECT * FROM yourTable " & (" WHERE " + varCriteria)

Open in new window

Author

Commented:
Here is the screen shot...

Okay Dale it looks impressive but I am at a loss on where I would do this and when...  Right now I am doing everything in query designer.  What forms are you referring too...
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Screen shot ??  MIA

Author

Commented:
Whoops
Doc1.docx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
So ... the first thing I notice is that you are Grouping by all fields.  Why is that?
Also ... you have a 'where' clause (which is actually a Having Clause) under Serial Number - which you are Group By also.
If you are filtering on SN ... why would you be grouping on it also?

Author

Commented:
Because we could have many lines with the same information and we just want one of them to show up.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Then use SELECT DISTINCTROW ...
And ... even if you continue to use Group By ...
Create a separate column for Serial Number ... and select WHERE (not Group By) and enter that criteria ... and remove it from the criteria on the Serial Number where you are current Grouping By ...

This way, it filters on the records you do want ... and then ... the  Grouping is done. Otherwise, the query processor waste a LOT of time grouping on records that will eventually get filtered out.

Just an fyi

Author

Commented:
Okay great, I did that...  I love to learn things and that helps. I could so use to help me streamline my database.  

 SO how do I allow my users to create a parameter query that allows multiple serial numbers at one time
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Well .... if you are talking about in the same Enter Parameter Prompt ... I don't think that is possible at the query level.  I've tried using the IN() operator before ... and no can do.
Let me try a couple of things ... but I'm not that hopeful ...

Author

Commented:
That would be wonderful.  By the way I went thru all my queries and I changed the query to have a distinct row.    Can we create a form and pass variables that way?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
" Can we create a form and pass variables that way?"
Well ... that is kind of what Dale showed you ...

Author

Commented:
I just don't understand how to do it.  But I am game for whatever you might find
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Well ... I messed around with Dales approach last night ... and could not get it to work.  As noted, I've tried that in the past ...
NOW ... not sure exactly what Dale is doing ... let me ping him ... and let's see what he comes up with.

Author

Commented:
I was able to create a form and pass one serial number thru.  So how do you pass when you want multiple serial numbers?  I want them to be able to paste the numbers from excel so we don't have to type them all individually.    Thank you for playing with it!
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Sure ... passing one number is no problem.  I pinged Dale ... let's see what he comes up with.
'Pasting is kind of the issue'. That implies a Text box (like you are trying). In the past, I would have a multi-select list box with the numbers, users would select one or more ... those get written to a small local table ... and query links to that. But that scheme does not allow pasting.
I guess ... you could ... do a similar thing ... have a datasheet view Form just one column that was connected to a small local table.  That table would join to your query on SN.  Users would paste N number of SNs into the datasheet view ... and maybe hit a button and the query runs.
Something like that.

Author

Commented:
Thank you for interpreting for me..  We could type them all in but hey let's shoot for the moon!
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
@Cres1121,

Assuming that we are looking for numeric values only, then let's relook that WHERE clause I provided you  instead of what I provided you previously, try a WHERE clause that looks like:

WHERE INSTR("," & Replace([Forms]![YourFormName].txtParameters, " ", "") & ",", "," & [FieldName] & ",") > 0

The reason I used the Replace command in this query is so that any spaces that a user inadvertently adds to the list of parameters would be eliminated.  Now, about the extra commas.  Assume that your user enters "13, 14, 16" into txtParameters. If you simply used:

WHERE Instr("13, 14, 16", "1") > 0

then if [FieldName] = 1, this would evaluate to TRUE, because there are three 1's in that parameter string.  But by adding the commas and removing the spaces, this would translate to:

WHERE INSTR(",13,14,16,", ",1,") > 0

and would evaluate to False.

Lets try this before we consider writing code to accomplish your task.

Author

Commented:
The serial numbers use text and numbers...    So can we assume that?  Can we also assume I have not idea where I am putting that and that I need more direction.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
The syntax provided above should work for values which do not contain spaces.  If the serial numbers include spaces, we would need to modify the replace function calls within the Instr( ) function, to ensure that spaces within the [Serial Number] are retained or stripped, whichever would work best.

Author

Commented:
Okay so Where do I put the Where statement?
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
You would make a copy of the query you mentioned in your previous post.
Then open that copy in design view, change it to SQL View, and then replace the WHERE clause that is currently there with:

WHERE INSTR("," & Replace([Forms]![YourFormName].txtParameters, " ", "") & ",", "," & [Serial Number] & ",") > 0

Note: Replace "YourFormName" with the name of the form you are using to enter these parameters.  Then replace "txtParameters" with the name of the actual control on that form where you are entering the multiple parameters.

Finally, open the form that you are using to run this query, enter whatever parameters in the Serial Number field and then run the query you just modified.  As long at the form that the query references is open, you should be able to run that query.

The sample file shows how to use this

MultipleParameters.accdb

Author

Commented:
I loved the example.  I am working on retro fitting it to my database.  I see you are using one text box then I put a , after each serial number.  In your educated though,t is there a way to make the text box like a datasheet where I could copy and paste in a list of serial numbers .  We get our information from excel so to retype them with a ,  would defeat what we are trying to do.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
That's cool Dale. Where as the IN() operator doesn't work, this kind of simulates it.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
I will post an example of what I was talking about ....
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007
Commented:
Open frmTempSN
Paste in your Serial numbers
Click Show Filtered SN List

BOOM
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
Yes, you could do that, but it would be a totally different concept.  See the attached file.MultipleParameters.accdb

Author

Commented:
I can't tell you how much I appreciate all the effort here.  Dale for the suggestions and Joe for interpreting them for me.  Joe also helped me with my query code to make it simpler.  I loved the examples that Dale gave me.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
glad to help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial