Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Oct 2006
    Location
    Virginia, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Crosstab Based on List Box Selections (Access 2002)

    I have a form containing 4 multiselection list boxes. One of the list boxes contains a list of course numbers. I would like to create a report that displays the courses (selected from the list box) across the top of the report and lists all attendees down the side of the report. I need the report to display an "X" or a "1" indicating which courses each attendee completed (like a matrix) and then a row at the end totaling the number of attendees for each course. I can create a crosstab displaying this information if I manually enter the course codes in the query window, but I cannot seem to incorporate the list box. I have entered code for the "Lost Focus" event of the list box to collect the selected items into one string and then store that data into a textbox on the form. My crosstab query is based on another query combining the registrant information with the course information. I tried entering [Forms]![SupplementalReports]![txtCourses] as the criteria for the course codes in this query, but it returns no records; but if I copy the text from the textbox on the form and paste it in as the query criteria instead of the reference, it works.

    Any help would be greatly appreciated! <img src=/S/angel.gif border=0 alt=angel width=15 height=21>
    Thanks,
    Tiffany

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Crosstab Based on List Box Selections (Access 2002)

    Welcome to Woody's Lounge!

    This is less simple than it might seem at first sight. You'll need some VBA code to pass the information to the query. In the attached zipped demo database, you'll find an example based on the Customers table from the Northwind database that comes with Access. There is a form frmDemo with a multi-select list box lbxCountries, and a crosstab query qrySelect.

    The module Module1 contains a function that is used in the query:

    Public Function IsSelected(varValue As Variant) As Boolean
    Dim lbx As ListBox
    Dim itm As Variant
    Set lbx = Forms!frmDemo!lbxCountries
    If IsNull(varValue) Then
    Exit Function
    End If
    For Each itm In lbx.ItemsSelected
    If lbx.ItemData(itm) = varValue Then
    IsSelected = True
    Exit Function
    End If
    Next itm
    Set lbx = Nothing
    End Function

    This function returns true if the value passed to it is one of the selected items in the list box in the form.

    The crosstab query contains a calculated column

    IsSelected([strCountry])

    with True in the Criteria line. The Totals option for this column is set to Where: it is only used to select records, it is not displayed in the query.

    Crosstab queries can display a totals column, but not a totals row.

  3. #3
    New Lounger
    Join Date
    Oct 2006
    Location
    Virginia, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab Based on List Box Selections (Access 2002)

    Thank you so much; that did the trick for that part of it and it was surprisingly simple! <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

    Now may I ask, would I do the same thing to limit the results based on any selections in the other three list boxes? The other three boxes have to do with the actual attendees. The users would like the ability to pull this report for the selected courses, but sometimes limit the results to one or more registrant types, classifications, and/or locations. These fields aren't displayed in the cross tab because I was limited to three fields. My original thought was to enter code to open the originating query in design view, enter the an "includes" statement in the criteria section, save, and close the query before opening the crosstab. I've done this before with reports to edit the filter based on selections on the form (until I discovered the WHERE clause), but I can't seem to do this (or the WHERE clause) with a query.

    Tiffany

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Crosstab Based on List Box Selections (Access 2002)

    Although the crosstab query wizard lets you specify only three fields as row headers, you can add more fields in design view. And you can add more calculated columns that serve only to filter the records returned by the query. But using four separate versions of the IsIncluded function will probably make the query quite slow.

    If you know SQL, you can create an SQL string that uses the conditions imposed by the list boxes, and set the SQL of a stored query to this string, for example using DAO. To use DAO, you must set a reference to the Microsoft DAO 3.6 Object Library in Tools | Options in the Visual Basic Editor.

    Here is a "simple" example:
    <code>
    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    Dim strIn As String
    Dim itm As Variant

    ' Get out if no items have been selected
    If Me.lbxCountries.ItemsSelected.Count = 0 Then
    MsgBox "Please select at least one country.", vbExclamation
    Me.lbxCountries.SetFocus
    Exit Sub
    End If

    ' Loop through selected items
    For Each itm In Me.lbxCountries.ItemsSelected
    strIn = ", " & Chr(34) & Me.lbxCountries.ItemData(itm) & Chr(34)
    Next itm
    ' Remove first ", "
    strIn = Mid(strIn, 3)
    ' Assemble SQL string
    strSQL = "SELECT * FROM tblCustomers WHERE strCountry In(" & _
    strIn & ")"
    ' Set DAO variables
    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs("qrySelect")
    ' Change SQL of query
    qdf.SQL = strSQL

    ' Release object memory
    Set qdf = Nothing
    Set dbs = Nothing
    </code>
    You can add more criteria to strSQL in code.

  5. #5
    New Lounger
    Join Date
    Oct 2006
    Location
    Virginia, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab Based on List Box Selections (Access 2002)

    Okay, that seemed to work, until I selected multiple items from the list box, in which case it only registered the last one. So I tried to play with the code a little, but I'm really not very good at it. My coding skills consist of copying code from other applications and tweaking it, apparently not always correctly. Anyway, below is the code I have. If I don't make any selections from the Registrant Type box, it works perfectly, but every time I make a selection, I get a message stating that "The Microsoft Jet Database Engine does not recognize 'strRegTypeCriteria' as a valid field name or expression." I hate to be a pain, but could you please look at the code below and steer me back in the right direction or at least explain where my logic is falling apart?

    Public Sub Command17_Click()

    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    Dim strRegTypeCriteria As String
    Dim vntItem As Variant
    Dim intcurrentitem As Integer

    'select registrant types
    If [Forms]![SupplementalReports]![lstRegistrantType].ItemsSelected.Count > 0 Then
    strRegTypeCriteria = "Registrant_Type IN ("
    For Each vntItem In Me.lstRegistrantType.ItemsSelected
    strRegTypeCriteria = strRegTypeCriteria & Chr(34) & Me.lstRegistrantType.ItemData(vntItem) & Chr(34) & ","
    Next
    strRegTypeCriteria = Left(strRegTypeCriteria, Len(strRegTypeCriteria) - 1) & ")"
    ' Assemble SQL string
    strSQL = "SELECT * FROM qryRegistrations WHERE strRegTypeCriteria"
    ' Set DAO variables
    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs("qrySQL")
    ' Change SQL of query
    qdf.SQL = strSQL
    Else
    ' Assemble SQL string
    strSQL = "SELECT * FROM qryRegistrations"
    ' Set DAO variables
    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs("qrySQL")
    ' Change SQL of query
    qdf.SQL = strSQL

    ' Release object memory
    Set qdf = Nothing
    Set dbs = Nothing

    End If
    DoCmd.OpenQuery "qryRegistrations_Crosstab"
    End Sub

    Thanks again!
    Tiffany

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Crosstab Based on List Box Selections (Access 2002)

    In the line

    strSQL = "SELECT * FROM qryRegistrations WHERE strRegTypeCriteria"

    strRegTypeCriteria shouldn't be inside the quotes. As it is now, you pass the name of the variable to the SQL string instead of its value. Change it to

    strSQL = "SELECT * FROM qryRegistrations WHERE " & strRegTypeCriteria

  7. #7
    New Lounger
    Join Date
    Oct 2006
    Location
    Virginia, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab Based on List Box Selections (Access 2002)

    Well don't I feel like an idiot! I cannot tell you how much I appreciate you and your knowledge! Thank you so much!
    Tiffany

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Crosstab Based on List Box Selections (Access 2002)

    Don't worry - it's a very common oversight. Most if not all developers have been bitten by it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •