Multiple values in one parameter field 1

[ciarra41]

Hey all,

Is there a way to use a parameter query to search for multiple values either text or number separate by commas in the same field? The parameter box will pop up and I can put in any number of values separate by commas.

 

[dhookom]

One kludge is to use something like:

SELECT *
FROM tblAutomobiles
WHERE Instr("," & [Enter Colors] & ",", "," & [Color] & ",") >0;

Entering "Blue,Red" will match all records where the color is either Blue or Red.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ciarra41]

But both parameter boxes are popping up. I would like for just one box show then enter the values like: "Red,Blue" or just one value like "Red" or as many as I like. This need manipulated to show text or number values.

-Thanks

 

[lespaul]

The issue is that when you enter something like:

SELECT * FROM tableName WHERE Color IN [Enter list of colors:]

and you enter:

Red, Blue, Green

What is passed as the parameter ends up in the SQL like this:

WHERE Color IN ("Red, Blue, Green")

instead of the proper syntax:

WHERE Color IN ("Red", "Blue", "Green")

you'll need to build the query dynamically. There are several examples in the FAQ area (see links at top of original post).

HTH



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[ciarra41]

It did actually work but I would like it to have multiple query parameters separate by commas from a single Prompt box. It still pops up with separate prompt boxes.

 

[dhookom]

What SQL "did actually work". Did you try my suggestion?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ciarra41]

yours dhookom but it needs to filter multiple parameters from "one" prompt box when I enter:

red,blue

 

[lespaul]

filter multiple parameters from "one" prompt box

you mean you want the user to enter:

Red, Blue, 1, 2

and then you want to search the Color Field AND some other field for those 4 values?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[ciarra41]

just the "color" field when the user enter

red,blue

 

[lespaul]

maybe you're getting two parameter pop ups because the parameter names don't match:

SELECT *
FROM tblAutomobiles
WHERE Instr("," & [Enter Colors] & ",", "," & [b][Color][/b] & ",") >0;

maybe if you change the second one to [Enter Colors] you'll get a single pop up for the parameters.

Leslie

 

[PHV]

It still pops up with separate prompt boxes.
Any chance you could post the SQL code and the text of the title bar of each prompt box ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ciarra41]

thanks lespaul both the parameter names need to match. Now how can it look for number fields, I think this only looks for text.

 

[lespaul]

ok, above you indicated that this needed to search a single field. How can a single field be both text and numbers? You can put numbers in a text field, but then it's text, not numeric.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[dhookom]

I'm with PH. Just post back some information that might have some value to those of us attempting to help you such as your SQL view of the query, some sample records, and field data types.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ciarra41]

Now I am getting this message: The specified Field "[CustomerId]" could refer to more than one table listed in the From clause of your SQL Statement." Is there a way to get around this.

SELECT AccountAll.PHONE, Accounting.CustomerId, Accounting.Cost
FROM Accounting INNER JOIN AccountAll ON Accounting.CustomerId = AccountAll.CustomerId
WHERE ((([AllAccounting].[CustomerId])=InStr("," & [CustomerId] & ",","," & [CustomerId] & ",")));

 

[lespaul]

yes, you have to tell the query WHICH table to get the Customer ID from in this section:

SELECT [i]AccountAll[/i].PHONE, Accounting.CustomerId, Accounting.Cost
FROM Accounting INNER JOIN AccountAll ON Accounting.CustomerId = AccountAll.CustomerId
WHERE ((([i][AllAccounting][/i].[CustomerId])=InStr("," & [b][CustomerId][/b] & ",","," & [b][CustomerId][/b] & ",")));

Should it look at the customerID that is in the Accounting table or the AccountAll table? Just add the correct table name . Also notice the italized section, what exactly is the table name?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[ciarra41]

Ok but how can I or you show me which table to point too. I'll be using the AccountAll table to point too.

SELECT AccountAll.PHONE, Accounting.CustomerId, Accounting.Cost
FROM Accounting INNER JOIN AccountAll ON Accounting.CustomerId = AccountAll.CustomerId
WHERE ((([AllAccounting].[CustomerId])=InStr("," & [CustomerId] & ",","," & [CustomerId] & ",")));

 

[lespaul]

ok, forget what I said above, I forgot what you were doing earlier....you'll need to first figure out if its AccountAll or AllAccounting:

SELECT AccountAll.PHONE, Accounting.CustomerId, Accounting.Cost
FROM Accounting INNER JOIN AccountAll ON Accounting.CustomerId = AccountAll.CustomerId
WHERE ((([AllAccounting].[CustomerId])=InStr("," & [b][Enter List of CustomerIds][/b] & ",","," & [b][Enter List of CustomerId][/b] & ",")));

you can't have the parameter prompt be the same as the field name.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[dhookom]

This is my first suggestion with your field and table names. If you had provided your select query SQL view and which field you wanted to search, this thread might have been completed after only a couple posts.

SELECT AccountAll.PHONE, Accounting.CustomerId, Accounting.Cost
FROM Accounting INNER JOIN AccountAll ON Accounting.CustomerId = AccountAll.CustomerId
WHERE InStr("," & [Enter Comma separated IDs] & ",","," & [Accounting.CustomerId] & ",")>0;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ciarra41]

Thanks dhookom, lespaul. This would have been solve on the third post if I just listed the SQL.

A double star for your patients...