using combo boxes for query filter

[DrJimmy]

I'm trying to use a set of combo boxes to act as filters. Once the user has chosen the different filters I then want to pass these into a make table query.

I'm currently using a piece of code to append the string value for the combo box to another string to create a SQL instruction - is there a better way of doing it than that?

Cheers

 

[ajetrumpet]

I'm trying to use a set of combo boxes to act as filters. Once the user has chosen the different filters I then want to pass these into a make table query.

Then just reference their values in the SQL statement using the correct syntax.

I'm currently using a piece of code to append the string value for the combo box to another string to create a SQL instruction - is there a better way of doing it than that?

There is no need for coding. Do you have a sample of what you are using?

 

[DrJimmy]

Yeah,

the filters I want are month, year, company and channel. From that I want to use them as criteria in a make table query using a table called orderDetail - does that make sense?

 

[DrJimmy]

I forgot to mention that if no company or channel is selected then it'll recognise that all company's and all channels should be included.

 

[ajetrumpet]

Yeah,

the filters I want are month, year, company and channel. From that I want to use them as criteria in a make table query using a table called orderDetail - does that make sense?

Of course it makes sense Jimmy. So, how do you want to filter them? By cascading them? By their separate value lists? You need to be more specific here.

 

[ajetrumpet]

OK, I think I see what you are getting at.

Do you already have the combo boxes set up, and populated with rowsources?

If you do, say so. All you need then is the SQL statement, which I believe is what you're asking about here.

 

[DrJimmy]

Yep. I've got 4 combo boxes, mthCombo;yrCombo;cmpCombo;chnCombo populated from 4 tables of similar names to create the list. month and year a defaulted to the current month and year. So the SQL would start, for example

SELECT sales_revenue FROM orderDetail WHERE........

Where the 4 combo boxes are the argument but it recognises that if cmpCombo and chnCombo are left blank then it'll bring back all records that match the month and year filter.

Obviously I'm a newbie so apologies if I'm not giving the right info...!

 

[ajetrumpet]

Where the 4 combo boxes are the argument but it recognises that if cmpCombo and chnCombo are left blank then it'll bring back all records that match the month and year filter.

This is the right info to give Jimmy. And you do it like this:

WHERE (([yearfield] = forms!yourform!cboyear OR 
   forms!yourform!cboyear IS NULL) AND

([monthfield] = forms!yourform!cbomonth OR 
   forms!yourform!cbomonth IS NULL) AND, etc...(for all the combos on the form)

See the pattern?

This is also outlined in a FAQ page here. This "QBF" method returns all records that have field values that match the specific combination of combo values filled in.

 

[DrJimmy]

That's great. Cheers for your help.