MultiSelect For Parameters

[jschmidt]

Hello Everyone,

I have an access application that has a listbox on a form. The list box is set to multiselect. I want to be able to use the selected items in a parameterized query that is predefined in access. The reason for this is that I need to run a transfertext macro that uses the query as a table. I have put the selected items from the listbox into a control on the form. The where clause looks like 'BABY FOOD' Or 'AUTOMOTIVE' if you select those from the list. Then I take what is in the control [Form]![frmWhatever]![txtBox] which = 'Baby Food' Or 'Automotive' and use it as a parameter in a predefined query. So the where clause for the query looks like [Form]![frmWhatever]![txtBox] and the parameter I have set is the same. When I click the button the where clause is built and put into the textbox and then the string in the textbox is used as parameters for the query. This doesn't work. The transfertext function gives me an empty CSV file unless I only select ONE item at a time... Any Reason for this?

[JKing]

Could you post the code for the query and the code you are using to build your where criteria? It sounds like you are building the where criteria improperly. If the string isnt right the query will return nothing and produce the empty file.

[MikeTheBike]

Hi

Hello Everyone,

I have an access application that has a listbox on a form. The list box is set to multiselect. I want to be able to use the selected items in a parameterized query that is predefined in access. The reason for this is that I need to run a transfertext macro that uses the query as a table. I have put the selected items from the listbox into a control on the form. The where clause looks like 'BABY FOOD' Or 'AUTOMOTIVE' if you select those from the list. Then I take what is in the control [Form]![frmWhatever]![txtBox] which = 'Baby Food' Or 'Automotive' and use it as a parameter in a predefined query. So the where clause for the query looks like [Form]![frmWhatever]![txtBox] and the parameter I have set is the same. When I click the button the where clause is built and put into the textbox and then the string in the textbox is used as parameters for the query. This doesn't work. The transfertext function gives me an empty CSV file unless I only select ONE item at a time... Any Reason for this?

Unfortunatly you cannot do this (to my knowledge - not sayng too much) because the query will look like this

"WHERE SomeFieldName = 'BABY FOOD' OR 'AUTOMOTIVE'
when it should look like this
"WHERE SomeFieldName = 'BABY FOOD' OR SomeFieldName = 'AUTOMOTIVE'

In short no record contains the value of "'BABY FOOD' Or 'AUTOMOTIVE' "
hence no records.

The only way I have found round this, when a stored query is absolutly necessary, is to build a query string in code and create a query definition on the fly, and that can be problematic in a multiuser envionment with a common front end.

But that is nt what you want hear!

My only suggestion, if possible/practical, would be to loop through the selected items in the list and perform you action(s) on each item, ie. single Where Field = '...'

Any help?


MTB

[jschmidt]

[quote=MikeTheBike]Hi


Unfortunatly you cannot do this (to my knowledge - not sayng too much) because the query will look like this

"WHERE SomeFieldName = 'BABY FOOD' OR 'AUTOMOTIVE'
when it should look like this
"WHERE SomeFieldName = 'BABY FOOD' OR SomeFieldName = 'AUTOMOTIVE'

In short no record contains the value of "'BABY FOOD' Or 'AUTOMOTIVE' "
hence no records.

The only way I have found round this, when a stored query is absolutly necessary, is to build a query string in code and create a query definition on the fly, and that can be problematic in a multiuser envionment with a common front end.

But that is nt what you want hear!

My only suggestion, if possible/practical, would be to loop through the selected items in the list and perform you action(s) on each item, ie. single Where Field = '...'

Any help?


Thanks fro the help. I figured out a way to do what I needed.