Parameter Query

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!
cres1121Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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.
1
cres1121Author Commented:
Thank you ..
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
So ...I'm not sure what you are asking here ' expand' ?
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

cres1121Author 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.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Can you post a screen shot of the query in the query designer as it is now - show the criteria as it is currently ?
0
Dale FyeOwner, Developing Solutions LLCCommented:
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

0
cres1121Author 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...
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Screen shot ??  MIA
0
cres1121Author Commented:
Whoops
Doc1.docx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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?
0
cres1121Author Commented:
Because we could have many lines with the same information and we just want one of them to show up.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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
0
cres1121Author 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
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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 ...
0
cres1121Author 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?
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
" Can we create a form and pass variables that way?"
Well ... that is kind of what Dale showed you ...
0
cres1121Author Commented:
I just don't understand how to do it.  But I am game for whatever you might find
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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.
0
cres1121Author 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!
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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.
0
cres1121Author Commented:
Thank you for interpreting for me..  We could type them all in but hey let's shoot for the moon!
0
Dale FyeOwner, Developing Solutions LLCCommented:
@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.
0
cres1121Author 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.
0
Dale FyeOwner, Developing Solutions LLCCommented:
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.
0
cres1121Author Commented:
Okay so Where do I put the Where statement?
0
Dale FyeOwner, Developing Solutions LLCCommented:
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
0
cres1121Author 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.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
That's cool Dale. Where as the IN() operator doesn't work, this kind of simulates it.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
I will post an example of what I was talking about ....
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Open frmTempSN
Paste in your Serial numbers
Click Show Filtered SN List

BOOM
1
Dale FyeOwner, Developing Solutions LLCCommented:
Yes, you could do that, but it would be a totally different concept.  See the attached file.MultipleParameters.accdb
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cres1121Author 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.
0
Dale FyeOwner, Developing Solutions LLCCommented:
glad to help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.