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!
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!
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!
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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.
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].TxtPara
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:
WHERE "," & [yourField] & "," IN "," & [Forms]![FormName].TxtPara
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)
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...
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...
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?
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?
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
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
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 ...
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 ...
Well ... that is kind of what Dale showed you ...
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.
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.
'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,
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]![YourFormN
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.
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]![YourFormN
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.
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]![YourFormN
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
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]![YourFormN
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
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.
Yes, you could do that, but it would be a totally different concept. See the attached file.MultipleParameters.accdb
Premium Content
You need an Expert Office subscription to comment.Start Free Trial