Build Criteria on Multiple Fields...

[Dongled]

I have a form, that has 2 buttons to buildcriteria to filter the form according to choices in 2 combo boxes. i.e. in combo box 1 you have a list of clients case status, in combo box 2 you have a list of company representatives.
At the moment, you chose what case status to view (new, offered, etc) and hit button 1. This filters form to show all cases offered etc.
You can also choose a name from the list of company representatives, hit button 2, and it filters the form to show all clients associated with that representative.

Now what I need is 1 button, so you can chose from both combo lists, and end up with a filter that shows all offered cases for 1 representative. In the Access help file they say to concatenate the criteria, only they don't say how to do it!! Anyone know what to do??

Cheers All

 

[FoxProProgrammer]

Are you actually using a filter or are you setting the recordsource for the form to a query that has a Where clause based on the selection in the combo box? If you are doing the latter, you would concatenate the two criteria and put And between them.

Example:

Select * From table Where field1 = 'cmbobox1.Value' And field2 = 'cmbobox2.Value'

The answer to your question also had a lot to do with how your tables are designed and related. Also note that the example above will work if the combo boxes contain text, but the syntax will be slightly different if they contain numbers.

You can use a button to trigger the event, or you can use the After Update event of the combo boxes. dz
dzaccess@yahoo.com

 

[Dongled]

I am actually using a frm.Filter method from visual Basic - using BuildCriteria to create, well the criteria! I have worked a way around the problem by hand-coding the criteria as a long list with AND inbetween each one, for the third button and it will concatenate the two combo options and display results OK, That is except when you try to use the Wildcard Asterisk. Then it does not seem to use LIKE"*" , it uses ="*" instead, which does not work, and which means that when I add the third set of filter options, I wont be able to pick 2 of the 3 combo options plus a wildcard option.

Sorry this seems so complicated - essentially I am trying to make the form as easy to use for my not-so-computer-literate bosses!