If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Multipule selection
I found a report I would like to simulate, but I don't know how to. Once you select the report there is a criteria box in which you can select multipule criteria. For example, if you wanted to run a report of the top internet users in a company by location, this box would have all the locations listed in a box with the ability to select several or just one location.
So, how can I do that? |
#2
|
|||
|
|||
Multipule selection
I hate writing the same code over and over like the code that loops through
the items selected in a multi-select list box. This function will accept a list box control as an arguement and return syntax like: " AND [ColorField] In ('Red', 'Black', 'Green') " It expects a specific format of your list box name. If no items are selected the function returns a zero length string. Save this function in a standard module. Don't name the module the same as the function name. A typical method of using this would be: Dim strWhere as String strWhere = " 1=1 " strWhere = strWhere & BuildIn(Me.lboTColor) strWhere = strWhere & BuildIn(Me.lboNEmpID) DoCmd.OpenReport "rptA", acViewPreview, , strWhere Function BuildIn(lboListBox As ListBox) As String 'send in a list box control object ' the list box name must begin with _ "lbo" and be followed by one character describing the data type _ "T" for Text _ "N" for Numeric or _ "D" for Date and then the 5th characters on for the _ field name ie: lboEmployeeID Dim strIn As String Dim varItem As Variant Dim strDelim 'used for delimiter 'Set the delimiter used in the IN (...) clause Select Case Mid(lboListBox.Name, 4, 1) Case "T" 'text data type strDelim = "'" 'double quote Case "N" 'numeric data type strDelim = "" Case "D" 'Date data type strDelim = "#" End Select If lboListBox.ItemsSelected.Count 0 Then strIn = " AND " & Mid(lboListBox.Name, 5) & " In (" For Each varItem In lboListBox.ItemsSelected strIn = strIn & strDelim & lboListBox.ItemData(varItem) & strDelim & ", " Next 'remove the last ", " and add the ")" strIn = Left(strIn, Len(strIn) - 2) & ") " End If BuildIn = strIn End Function You could also add arguments to the function that contain the Field Name and Field Delimiter rather than storing these two pieces of information in the listbox control name. The function might then look like Function BuildIn(lboListBox As ListBox, _ strField as String, strDelimiter as String) _ As String 'etc A call to the function could be: strWhere = strWhere & _ (BuildInMe.lboColor, "Color", """" ) -- Duane Hookom MS Access MVP "jewels" wrote in message ... I found a report I would like to simulate, but I don't know how to. Once you select the report there is a criteria box in which you can select multipule criteria. For example, if you wanted to run a report of the top internet users in a company by location, this box would have all the locations listed in a box with the ability to select several or just one location. So, how can I do that? |
#3
|
|||
|
|||
Multipule selection
Duane,
I will try this, thank you! jewels |
Thread Tools | |
Display Modes | |
|
|