Accessing a customer list in the criteria of a query

[shaunacol]

I have a query which relates only to certain customers so I have listed the names of these customers in the 'criteria' section of the 'customer ' column within my query. However, I do not really want the user to have to access the query to change the customer list (although this may not happen often I think there must be a better way for me to reference the customer list from a more user friendly place). Any help appreciated.

 

[TheAceMan1]

How are ya shaunacol . . .

[blue] . . .although this may not happen often I think there must be a better way for me to reference the customer list from a more user friendly place).[/blue]

You could use a form with a [blue]multiselect[/blue] listbox of customer names. Using the listbox and some code you can directly edit the SQL of the query and view its results.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[shaunacol]

Thanks for your reply. I think you are saying that I can change the criteria of my query via a list box in a form. That sounds exactly like something I would want to do but wouldnt know where to start...

 

[TheAceMan1]

shaunacol . . .

Post the SQL of the query! Also, how are you using it? ... query view, form recordsource, what?

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[dhookom]

Check the FAQs of this forum to find solutions for using a multi-select list box in the criteria of a report.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

If this is something that changes very infrequently, you may not want to force the user to select from a list every time they want to run the query. Instead (or in conjunction with the multi select list box) save the selections to a seperate table, "tblSelected" then the data is persistent. Use a subquery to return only those customers in the tblSelected. Something like

SELECT * from someTable WHERE customerID IN (SELECT customerID from tblSelected)

 

[MajP]

Probably the easiest way to do this is add a yes/no field to your customers table, "blnSelected". Then you can make a form to select the customers you want. You will not have to create a new table. Then you query becomes

SELECT * from someTable WHERE customerID IN (SELECT customerID from tblCustomers where blnSelected = True)

 

[shaunacol]

Thanks so much for all these great idea! I am leaning towards creating a tick box directly in my customer table to identify the specific ones. That is such an easy way to do it - thanks for all the help!