Multiple Values in 1 parameter?

[snoto]

Hey all, is it possible to enter multiple values in 1 paramater?

At the moment, I have 2 parameters where I enter 1 value in each to find the records.

Example of what I want:

Enter colors:
Blue, Red

Is it possible to have a comma delimiter or even a space delimiter, and how would I go about implementing this?

 

[Banana]

You could certainly enter "Blue, Red" but that doesn't mean query will be able to parse it individually- it'd be looking for literal "Blue, Red".

Are you trying to do this:

SELECT ... WHERE someColumn IN ([MyParameter]);

?

Maybe if you post what you are trying to do with the query, we can provide a useful suggestion.

 

[snoto]

Ok I have a select query, where there are 2 paramaters. I need one of the paramaters to accept mulitple values.

At the moment, I have 2 prompt messages, one asking for an Enter Trace from Requirement, where i would input PR. But want I want to do is to input PR, CR. SO that it would search results for both PR or CR if you get me.

Cheers

 

[Banana]

A cheap band-aid solution off the top of my head (and untested!): Use InStr():

WHERE InStr(RqRequirements.RequirementPrefix, [Enter Trace From Requirement Type]) > 0

Be aware that if this is for a large table, performance will be abysmal and you can't use an index.

But more robust solution would be to use VBA to build SQL just in time:

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("SELECT ... WHERE RqRequirements.RequirementPrefix IN (" & MyInputVariable & ");"

and assign the recordset to the form or whatever. (If it's for report, then we'd need to do it differently because reports doesn't like that method)

 

[snoto]

Tried the first one, don't think it worked. Not really sure how to do the second one (really new to MS access)

Let me explain my problem from the top (if I've been unclear). I have a query that prompts 2 paramaters, these 2 paramters are matched and then are used to find records in a database.

Parameters I have are: Enter from Requirements, Enter to Requrements.
I would enter PR for the first one, then like REL for the second one, it will then like show all the records that match those two paramaters from the table.

What i want is for the first input, Enter from requirements, is to input two values, like PR and CR, so i would input PR, CR (seperated by comma in prompt) etc and input REL for the second parameter prompt, I want it to find PR and REL and CR and REL records, if you get me?

here's my current sql for the query (this is not for a report or anything just a query)

removed

 

[Banana]

I see.

I think the first one didn't work because I may have had InStr()'s parameters in wrong place. Try that:

InStr([Enter Trace From Requirement Type], RqRequirements.RequirementPrefix) > 0

For some reasons I always manage to get InStr() backward... Oh well.


As for the SQL itself, it should be noted that if we were to hard-code the criteria, it ought to look like this:

WHERE
   (
      rqrequirements.requirementprefix LIKE "PR*" OR 
      rqrequirements.requirementprefix) LIKE "CR*"
   ) 
   AND 
   (
      rqrequirements_1.requirementprefix) LIKE "REL*"
   );

Which is slightly different from what you are trying to do with your parameter query.

May I suggest that you use a form instead to enter the criteria and build your query based on the form? It's generally considered best practices to never expose users to the tables & queries directly and work through forms & reports.

 

[snoto]

Hey, still didnt work But thanks for the responses, I don't really want to make a form but I'll guess I'll have to...

 

[snoto]

OK, so i've made a listbox that allows me to select mulitple values for 1 paramater, however it doesn't work, can anyone help me on this?

 

[Banana]

why don't you post the VBA you have for the listbox? Alternatively, see if helps.

 

[snoto]

Ive checked out that page, but like, I have 2 list boxes, I select 2 values in listbox 1 and it needs to match listbox2 to display the records (so first selection in listbox1 + listbox2, second selection in listbox1+listbox2 etc), the examples given only have one listbox!

+ my form works perfectly only for 1 selection in listbox1

 

[wilpeter]

I realize I'm likely 'way out of line by interrupting, your initial post indicated you were using "red, blue" to mean "red" or "blue", so I'm going to venture that even without SQL, VBA, or whatever, the basics for criteria remain:

 

[snoto]

I realize I'm likely 'way out of line by interrupting, your initial post indicated you were using "red, blue" to mean "red" or "blue", so I'm going to venture that even without SQL, VBA, or whatever, the basics for criteria remain:

Yep thats it basically. So far I have a multi list box, these are used to select paramater values for my query.


When I select both CR and PR and then REL, it works and finds all the recrods I want. But if I choose PR and REL only, it doesn't work :S (Only works for non-multi list box) However I need to use multi list box!

Can anyone tell me what to put in my VBA command/run query button ?

 

[boblarson]

http://www.baldyweb.com/multiselect.htm

OK, so i've made a listbox that allows me to select mulitple values for 1 paramater, however it doesn't work, can anyone help me on this?

For multiple selections on a listbox you need to use VBA code to get the parameters set up right. You can't reference a multiselect listbox by itself in a query's criteria because it doesn't return a value when set as multi-select. It will only return a NULL. So, you will need something like this to do it.

 

[Banana]

It's hard to tell you what to put in your VBA if we don't even know what it contains.

Try and post your VBA. Use code tags so it shows up nicely. You can create a code tag by using 'Go Advanced' and choosing code from the text editor, or type those nesting your code:

[ code]

'Your VBA goes here...

[ /code]

Remove the space between the opening [ and the value so it works.

 

[snoto]

Sorry im a newbie, but I appreciate all the help im getting

This is my code for my run query button

Option Compare Database
Private Sub run_Click()
On Error GoTo Err_run_Click
    Dim stDocName As String
    stDocName = "qryTest"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
Exit_run_Click:
    Exit Sub
Err_run_Click:
    MsgBox Err.Description
    Resume Exit_run_Click
 
End Sub

qryTest is:

removed

I'm guessing I have to incoporate the qryTest into the VBA (in my run button), but I'm really confused as to how to do so :s

 

[snoto]

This is a code I googled and is similar to what I need (I think)

   Set DB = CurrentDb()
   Set Q = DB.QueryDefs("MultiSelect")
   Q.SQL = "Select * From Orders Where [CustomerID] In(" & Criteria & _
     ");"
   Q.Close

Where criteria is the end result of a loop for the multi list box. How would I go about adding in a second where? so that my paramater 2 is met ?

 

[boblarson]

Q.SQL = "Select * From Orders Where [CustomerID] In(" & Criteria & _
") AND [Some Other Field] In(" & Criteria2 & ");"

 

[snoto]

removed

This is what I have at the moment, but its not returning any results, anyone know what the problem is?

 

[snoto]

Finally got it all done, cheers everyone for their help!