Trying to create a multiple select on same field

[Vonblack]

Hi all,



So.... my many attempts at creating a multiple select parameter field to have failed.

I have however stumbled access query criteria which will enable me to create 4-5 combo boxes from the same field (e.g. select1, Select2, Select3, Select4 etc...) which when passed to my query will show the relevant records for each selection (See below).

In ([Forms]![FormName]![Select1],[Forms]![FormName]![Select2],[Forms]![FormName]![Select3],[Forms]![FormName]![Select4])

What I cannot work out is how to show all records if I leave my combo box's blank.

I've tried the below which do not work...

Like "*" and In ([Forms]![FormName]![Select1],[Forms]![FormName]![Select2],[Forms]![FormName]![Select3],[Forms]![FormName]![Select4])

In ([Forms]![FormName]![Select1],[Forms]![FormName]![Select2],[Forms]![FormName]![Select3],[Forms]![FormName]![Select4]) and "*"

In ([Forms]![FormName]![Select1],[Forms]![FormName]![Select2],[Forms]![FormName]![Select3],[Forms]![FormName]![Select4]) or "*"


Kind Regards
Steve

[June7]

Use VBA to build and apply filter. Review http://allenbrowne.com/ser-62.html.

A multi-select listbox could be used instead of 4 comboboxes.

[Vonblack]

Thanks June7...

I think ive managed to replicate and sort it from this website (http://www.baldyweb.com/multiselect.htm) - Thanks to Baldy

My query now uses a combination of traditional text boxes for single parameters. These are referenced in the query using the normal method i.e "=Forms]![FormName]![Select1]"

With a list box that passes my multi select parameters directly when the report opens rather than through the query.

i.e..

Private Sub cmdOpenReport_Click()


On Error GoTo Err_cmdOpenReport_Click


Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant


'make sure a selection has been made
If Me.lstEmployees.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 status"
Exit Sub
End If


'add selected values to string
Set ctl = Me.lstEmployees
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & ctl.ItemData(varItem) & ","
Next varItem
'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)
'open the report, restricted to the selected items
DoCmd.OpenReport "rptEmployees", acPreview, , "ID IN(" & strWhere & ")"


Exit_cmdOpenReport_Click:
Exit Sub


Err_cmdOpenReport_Click:
MsgBox Err.Description
Resume Exit_cmdOpenReport_Click




End Sub

[pbaldy]

Post 3 was moderated, I'm posting to trigger email notifications.