String of comma deritive text in a form text box to use in a Access SQL/ query?

Question

From my other post where I have trouble getting data from a multiselect list box to a query I have put the results in a text box. How can I use this to run a query?

Answer 1

Sorry I don't know Access - but one of the following should work:

As your fetching the results back from the list box build up a SQL statement with something like ( you can use either 'and' or 'or' depending if it's cumulative or simply one on the options )

select field1, field2 from table1 where field3='value1' and field3='value2'

So each time you get a selection add on to your existing base statement starting with

'select field1, field2 from table1 '

the first time add ' where ' and then your clause field3=....

all subsequent times add ' and ' ( or ' or ') then your clause field3=...

Once you've add all the possibilities in then execute the statement generated.



Alternatively ( and preferably if Access supports it ) build a statement in a similar way but the statement will end up something like:

'select field1, field2 from table1 where field3 in ('value1', 'value2') '

this will return any row where field3 is any of the values ( same as 'or' in above ).

Answer 2

String of comma derivative text in a form text box to use in a Access SQL/ query?



There are several methods you could deploy, all depending on your own knowledge and how complex the criteria is.



To start, you may want to create a link between the Form and the Query so that the the control on the form (text box) which contains only the value (not including operators etc) that you want to supply is connected as a parameter (query).



For example, if the textbox is called 'TextBox1' from a Form called 'Form1'; in the query itself the criteria of the field which this applies to will have something like



[Forms]![Form1]![TextBox1]



You can add operators with this field too so for example:



[Forms]![Form1]![TextBox1] Or [Forms]![Form1]![TextBox2]



Which points to two textbox controls from the Form.



If you are building a string statement which will be supplied as the SQL then ideally you will need to code (VBA) this to call a query or any other object as the output normally using the application keyword 'DoCmd'.



The last example requires a little more information and knowledge in order to be successful.



Hope this helped.

Ben Beitler

Answer 3

Texted