Parameter Query

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.

Thank you ..

So ...I'm not sure what you are asking here ' expand' ?

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.

Can you post a screen shot of the query in the query designer as it is now - show the criteria as it is currently ?

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)

Select all Open in new window

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

Screen shot ??  MIA

Whoops
Doc1.docx

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?

Because we could have many lines with the same information and we just want one of them to show up.

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

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

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

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?

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

I just don't understand how to do it.  But I am game for whatever you might find

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.

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!

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.

Thank you for interpreting for me..  We could type them all in but hey let's shoot for the moon!

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

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.

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.

Okay so Where do I put the Where statement?

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.

That's cool Dale. Where as the IN() operator doesn't work, this kind of simulates it.

I will post an example of what I was talking about ....

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.

glad to help.