Query problem - passing criteria

[ray147]

i have a query in design view to which i'd like to pass a criteria value for one field... I have some routine in VBA which creates a string as below:

"SupplierName1" And "SupplierName2" And "SupplierName3"

I am passing this to a textbox on the form exactly as shown above.

I would now like to use this value as criteria input for the query. However, it seems that Access doesn't recognise this....if I write just SupplierName1 in the textbox, the query works fine but as soon as I put in the quotes or else more than one supplier name, it does not work...

any ideas how can i resolve this?

Just FYI, the string of names of suppliers is being generated through string manipulation from a listbox using a value list.

Thanks in advance.

 

[RuralGuy]

Would you post your SQL for the query for us to review please?

 

[antomack]

Rather than typing in the supplier names why not provide a listbox of the supplier names and allow multiple selection from this.

If you look at the example posted below you will see how to build your query string then from the items selected in the listbox
http://www.access-programmers.co.uk/forums/showthread.php?t=99353

 

[ray147]

i've checked out that example database and managed to implement the same concept of using a BuildFilter function which solved my issue...

i'm not using a listbox with multiple selection ...i'm using a combo box (with a query) and when a supplier is chosen, it's appended to a listbox. reason is that the suppliers list is very long, so it would be a bit difficult for the user to browse thru the list box..

thanks, that example you posted was very helpful!