Avatar of cres1121
cres1121
 asked on

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!
Microsoft Access

Avatar of undefined
Last Comment
Dale Fye

8/22/2022 - Mon
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

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.
cres1121

ASKER
Thank you ..
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

So ...I'm not sure what you are asking here ' expand' ?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
cres1121

ASKER
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 - Former Microsoft Access MVP)

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 Fye

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

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
cres1121

ASKER
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 - Former Microsoft Access MVP)

Screen shot ??  MIA
cres1121

ASKER
Whoops
Doc1.docx
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

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?
cres1121

ASKER
Because we could have many lines with the same information and we just want one of them to show up.
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
cres1121

ASKER
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 - Former Microsoft Access MVP)

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 ...
cres1121

ASKER
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?
Your help has saved me hundreds of hours of internet surfing.
fblack61
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

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

ASKER
I just don't understand how to do it.  But I am game for whatever you might find
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
cres1121

ASKER
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 - Former Microsoft Access MVP)

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.
cres1121

ASKER
Thank you for interpreting for me..  We could type them all in but hey let's shoot for the moon!
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Dale Fye

@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.
cres1121

ASKER
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 Fye

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
cres1121

ASKER
Okay so Where do I put the Where statement?
SOLUTION
Dale Fye

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
cres1121

ASKER
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 - Former Microsoft Access MVP)

That's cool Dale. Where as the IN() operator doesn't work, this kind of simulates it.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

I will post an example of what I was talking about ....
SOLUTION
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Dale Fye

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
cres1121

ASKER
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 Fye

glad to help.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.