SQL Help for Multiple Combo Box query

[noaccessguru]

Hi guys,



Hoping someone can help me out with this as it’s an SQL question and I’m still only in the very early days of self learning SQL!

I have a Form that has 4 combo boxes on it that I have set to requery when the one above is updated so that a user can drill down to some records based on their selections (this is all working fine)

My Combo boxes are:

CmnName
Product
Manufacturer
Supplier

I have created a query that I want to run based on the selections from the Combo boxes.

I want the user to be able to select ANY of the combo boxes individually or multiple selections:

Eg:
If the user selects CmnName & Product it will bring back all the records that match those 2 criteria

OR if the user select Product & Manufacturer it will bring back all the records for those 2

OR if the user Only selects Manufacturer it will bring back all the records for that Manufacturer…..

How do I write the SQL for this to work for the above scenarios??? (I need it so that they can run all off the options individually too)

The SQL I have done doesn’t seem to work?
The query is attached to a command button and It brings back the correct results if I select CmnName & Product and then run it, but comes back blank if I select anything else!?

Here is the SQL from my query:

SELECT Tbl_Materials_MASTER.ID, Tbl_Materials_MASTER.[Spec No], Tbl_Materials_MASTER.[Common Name], Tbl_Materials_MASTER.[Material Spec], Tbl_Materials_MASTER.[General Material Spec], Tbl_Materials_MASTER.Product, Tbl_Materials_MASTER.Code, Tbl_Materials_MASTER.Supplier, Tbl_Materials_MASTER.Manufacturer, Tbl_Materials_MASTER.Status, Tbl_Materials_MASTER.[Religious/Dietary Status], Tbl_Materials_MASTER.[Country Of Origin], Tbl_Materials_MASTER.MSDS
FROM Tbl_Materials_MASTER
WHERE (((Tbl_Materials_MASTER.[Common Name])=Forms!Frm_SearchMaterialsCombo.CmbCmnName) And ((Tbl_Materials_MASTER.Product)=Forms!Frm_SearchMaterialsCombo.CmbProduct)) Or (((Tbl_Materials_MASTER.Product)=Forms!Frm_SearchMaterialsCombo.CmbProduct) And ((IsNull(Forms!Frm_SearchMaterialsCombo.CmbCmnName))<>False)) Or (((Tbl_Materials_MASTER.[Common Name])=Forms!Frm_SearchMaterialsCombo.CmbCmnName) And ((IsNull(Forms!Frm_SearchMaterialsCombo.CmbProduct))<>False)) Or (((IsNull(Forms!Frm_SearchMaterialsCombo.CmbCmnName))<>False) And ((IsNull(Forms!Frm_SearchMaterialsCombo.CmbProduct))<>False)) Or (((Tbl_Materials_MASTER.Manufacturer)=Forms!Frm_SearchMaterialsCombo.CmbManufac) And ((IsNull(Forms!Frm_SearchMaterialsCombo.CmbManufac))<>False)) Or (((Tbl_Materials_MASTER.Supplier)=Forms!Frm_SearchMaterialsCombo.CmbSupplier) And ((IsNull(Forms!Frm_SearchMaterialsCombo.CmbSupplier))<>False));

(This is not exactly what I wrote it, but this is what it changed it to!)

Hope this all makes sense!

Any help is muchly appreciated...and apologies if this has been covered before!

Thanks in advance

[noaccessguru]

sorry dont know if that SQL posted properly?

[rpeare]

I would suggest using the like command

so you would have this instead:

(Tbl_Materials_MASTER.[Common Name]) like "*" & Forms!Frm_SearchMaterialsCombo!CmbCmnName) & "*"

This would find all matches where the strings are populated and give you every possible record where there was something missing from your selection criteria.

So if you had this type of setup if you had NOTHING selected it would return all rows in your database, if you just had the customer name selected it would return everything for the customer, and so on, refining your search as you went down.

[noaccessguru]

Hi rpeare,

Thanks for your reply and your help with this!

I did do it with the 'Like' statements and this works well (this was going to be my backup if the exact matches didnt work)

So do you suggest this is a better way of doing it than exact matches for the combo boxes?

This is the SQL I had for it with the Like statements....Is this written correctly? (not sure if all the brackets etc are all in the right spot)

WHERE (((Tbl_Materials_MASTER.[Common Name]) Like "*" & [Forms]![Frm_SearchMaterialsCombo].[CmbCmnName] & "*") AND ((Tbl_Materials_MASTER.Product) Like "*" & [Forms]![Frm_SearchMaterialsCombo].[CmbProduct] & "*") AND ((Tbl_Materials_MASTER.Manufacturer) Like "*" & [Forms]![Frm_SearchMaterialsCombo].[CmbManufac] & "*") AND ((Tbl_Materials_MASTER.Supplier) Like "*" & [Forms]![Frm_SearchMaterialsCombo].[CmbSupplier] & "*"));

Any additional help is appreciated

[rpeare]

Sure, there are always options, but it depends on how much effort and programming you want to do.

One way is to create a query for each of the possibilities you may encounter
i.e. one query that just queries by the first combo box as a criteria, a second query that queries by the first two combo boxes as a critera, etc. Then when you go to process the results you would just change the control source of your list box to match the correct query based on the number of combo boxes that are not null.

Another way is to use VB script to build your SQL statement on the fly based on the number of items chosen and, again, change the control source of the list box based on that new SQL statement.

Personally I favor the second one because I hate cluttering my database windows but if you're not comfortable with the programming end the first is far easier to deal with.

[ssanfu]

Also, "The Access Web" site has an example of how to return all records if the criteria is null.

See: "Queries: Use a parameter to return all records if Null"

http://access.mvps.org/access/queries/qry0001.htm

[noaccessguru]

Thanks heaps for your help guys.

rpeare - I might play around with the first option you suggested, as Im not too comfortable with VB yet, just in the very early learning stages of how that all works Thanks again.

Thanks ssanfu, That link is very handy!! Will definitley be using that.
Appreciate your help guys!

Cheers
Laura