MultiSelect from Form into Query

[MKH2001]

Is there any way of selecting multi values from drop down combo box (or listbox) to run in a query?

SELECT tbl_AVWorkOrders.AVWorksorderNo, tbl_AVWorkOrders.UnitID, tbl_AVWorkOrders.DefectType, tbl_AllSLMastr.STNAME, tbl_AllSLMastr.ADDRQUAL, tbl_AllSLMastr.TOWN, tbl_AnnualServInsp.DBName, tbl_AnnualServInsp.Date, tbl_AnnualServInsp.SLSRRoute, tbl_AVWorkOrders.[HERMIS NO] INTO tbl_OutstandingAVWorkOrders
FROM tbl_GroupAsset INNER JOIN (((tbl_AllSLMastr INNER JOIN tbl_AnnualServInsp ON tbl_AllSLMastr.UNITID = tbl_AnnualServInsp.UNITID) INNER JOIN tbl_AVWorkOrders ON tbl_AnnualServInsp.AnnualServInspNo = tbl_AVWorkOrders.AnnualServInspNo) INNER JOIN Personell ON tbl_AnnualServInsp.DBName = Personell.ID) ON tbl_GroupAsset.COMPKEY = tbl_AllSLMastr.COMPKEY
GROUP BY tbl_AVWorkOrders.AVWorksorderNo, tbl_AVWorkOrders.UnitID, tbl_AVWorkOrders.DefectType, tbl_AllSLMastr.STNAME, tbl_AllSLMastr.ADDRQUAL, tbl_AllSLMastr.TOWN, tbl_AnnualServInsp.DBName, tbl_AnnualServInsp.Date, tbl_AnnualServInsp.SLSRRoute, tbl_AVWorkOrders.[HERMIS NO]
HAVING (((tbl_AVWorkOrders.DefectType) Like ([Forms]![frm_OutstandingAVWO]![defect] & "*")) AND ((tbl_AnnualServInsp.DBName) Like [Forms]![frm_OutstandingAVWO]![Inspector]) AND ((tbl_AnnualServInsp.Date) Between [Forms]![frm_OutstandingAVWO]![datefrom] And [Forms]![frm_OutstandingAVWO]![dateto] & " 23:59:59") AND ((tbl_AnnualServInsp.SLSRRoute) Like [Forms]![frm_OutstandingAVWO]![Route]) AND ((tbl_AVWorkOrders.[HERMIS NO]) Is Null));

Is what I currently use to run query based on single selection, but is there anyway to allow my users to run query

(tbl_AVWorkOrders.DefectType) Like ([Forms]![frm_OutstandingAVWO]![defect] & "*"))

Is the dropdown combobox field in the form I would like to enable so they can select multiple values.

Any ideas?

 

[PHV]

Only a ListBox may allow multiple selections.
Furthermore the ItemsSelected collection is available thru VBA code only, i.e. you have to build the WHERE clause on the fly by code.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[FancyPrairie]

It appears that your query would not only pull info from 1 or more multi-select list boxes, but also includes data ranges. Therefore, checkout this thread faq181-5497 It contains a function that will build and return the WHERE clause for you. It works on single and multi-select list boxes, combo boxes, text boxes, date ranges, option groups, and check boxes. You only have to do 3 things to make it work:

1. Create a new module (name it something like basBuildWhere) and copy and paste the functions from the FAQ into the new module.
2. Define your tag properties as specified in the FAQ
3. Open the report as specified in the FAQ