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
|
|||
|
|||
Crosstab Query Building Report
I have a form with several multi list boxes. Once a row(s) is selected, a
WHERE string is built, deletes an existing Query, and then builds a new Query1 with that Where string criteria. I then build a Crosstab query with the Query1 criteria. This works very well and vey fast. Instead of opening the Crosstab Query (as code shows), I would like to display the retrieved data in an AutoReport: Tabular design. Because the row values in my lstGroup box will change (required selection; have yet to code), I will need to delete/create/replace the existing report with the new values in the Crosstab Query because the Column Headings in the report must change with row selectionin lstGroup. Any ideas on how I might accomplish is gtreatly appreciated. Thanks. Private Sub cmdOK_Click() On Error GoTo Err_cmdOK_Click Dim varItem As Variant Dim strWhere As String Dim strWhere1 As String Dim strWhere2 As String Dim strWhere3 As String Dim strWhere4 As String Dim strWhere5 As String Dim strWhere6 As String Dim strWhere7 As String Dim strWhere8 As String Dim strWhere9 As String Dim lngLen As Long Dim strDelim As String Dim db As DAO.Database Dim qdf As DAO.QueryDef Dim strSQL As String Dim strDoc As String 'Build WHERE string With Me!lstGroup For Each varItem In .ItemsSelected If Not IsNull(varItem) Then strWhere1 = strWhere1 & strDelim & .ItemData(varItem) & strDelim & "," End If Next varItem End With lngLen = Len(strWhere1) - 1 If lngLen 0 Then strWhere1 = "[GroupID] IN (" & Left$(strWhere1, lngLen) & ") " End If With Me!lstClass For Each varItem In .ItemsSelected If Not IsNull(varItem) Then strWhere2 = strWhere2 & "'" & strDelim & .ItemData(varItem) & strDelim & "'," End If Next varItem End With lngLen = Len(strWhere2) - 1 If lngLen 0 Then strWhere2 = "[CO] IN (" & Left$(strWhere2, lngLen) & ") " End If With Me!lstBrand For Each varItem In .ItemsSelected If Not IsNull(varItem) Then strWhere3 = strWhere3 & strDelim & .ItemData(varItem) & strDelim & "," End If Next varItem End With lngLen = Len(strWhere3) - 1 If lngLen 0 Then strWhere3 = "[SupplierID] IN (" & Left$(strWhere3, lngLen) & ") " End If With Me!lstJGroup For Each varItem In .ItemsSelected If Not IsNull(varItem) Then strWhere4 = strWhere4 & "'" & strDelim & .ItemData(varItem) & strDelim & "'," End If Next varItem End With lngLen = Len(strWhere4) - 1 If lngLen 0 Then strWhere4 = "[JobGroup] IN (" & Left$(strWhere4, lngLen) & ") " End If With Me!lstMake For Each varItem In .ItemsSelected If Not IsNull(varItem) Then strWhere5 = strWhere5 & "'" & strDelim & .ItemData(varItem) & strDelim & "'," End If Next varItem End With lngLen = Len(strWhere5) - 1 If lngLen 0 Then strWhere5 = "[Make] IN (" & Left$(strWhere5, lngLen) & ") " End If With Me!lstModel For Each varItem In .ItemsSelected If Not IsNull(varItem) Then strWhere6 = strWhere6 & "'" & strDelim & .ItemData(varItem) & strDelim & "'," End If Next varItem End With lngLen = Len(strWhere6) - 1 If lngLen 0 Then strWhere6 = "[Model] IN (" & Left$(strWhere6, lngLen) & ") " End If With Me!lstEngine For Each varItem In .ItemsSelected If Not IsNull(varItem) Then strWhere7 = strWhere7 & "'" & strDelim & .ItemData(varItem) & strDelim & "'," End If Next varItem End With lngLen = Len(strWhere7) - 1 If lngLen 0 Then strWhere7 = "[Engine] IN (" & Left$(strWhere7, lngLen) & ") " End If With Me!lstType For Each varItem In .ItemsSelected If Not IsNull(varItem) Then strWhere8 = strWhere8 & strDelim & .ItemData(varItem) & strDelim & "," End If Next varItem End With lngLen = Len(strWhere8) - 1 If lngLen 0 Then strWhere8 = "[TypeID] IN (" & Left$(strWhere8, lngLen) & ") " End If With Me!lstEGroup For Each varItem In .ItemsSelected If Not IsNull(varItem) Then strWhere9 = strWhere9 & "'" & strDelim & .ItemData(varItem) & strDelim & "'," End If Next varItem End With lngLen = Len(strWhere9) - 1 If lngLen 0 Then strWhere9 = "[Description] IN (" & Left$(strWhere9, lngLen) & ") " End If strWhere = strWhere1 If Len(strWhere) 0 And Len(strWhere2) 0 Then strWhere = strWhere & " AND " & strWhere2 Else strWhere = strWhere & strWhere2 End If If Len(strWhere) 0 And Len(strWhere3) 0 Then strWhere = strWhere & " AND " & strWhere3 Else strWhere = strWhere & strWhere3 End If If Len(strWhere) 0 And Len(strWhere4) 0 Then strWhere = strWhere & " AND " & strWhere4 Else strWhere = strWhere & strWhere4 End If If Len(strWhere) 0 And Len(strWhere5) 0 Then strWhere = strWhere & " AND " & strWhere5 Else strWhere = strWhere & strWhere5 End If If Len(strWhere) 0 And Len(strWhere6) 0 Then strWhere = strWhere & " AND " & strWhere6 Else strWhere = strWhere & strWhere6 End If If Len(strWhere) 0 And Len(strWhere7) 0 Then strWhere = strWhere & " AND " & strWhere7 Else strWhere = strWhere & strWhere7 End If If Len(strWhere) 0 And Len(strWhere8) 0 Then strWhere = strWhere & " AND " & strWhere8 Else strWhere = strWhere & strWhere8 End If If Len(strWhere) 0 And Len(strWhere9) 0 Then strWhere = strWhere & " AND " & strWhere9 Else strWhere = strWhere & strWhere9 End If Set db = CurrentDb '*** create the query based on the information on the form strSQL = "SELECT qryxCatalog.* FROM qryxCatalog " strSQL = strSQL & " WHERE " & strWhere '*** delete the previous query db.QueryDefs.Delete "qryxCatalog1" Set qdf = db.CreateQueryDef("qryxCatalog1", strSQL) '*** open the query strDoc = "qryxCatalogCrosstab" DoCmd.SetWarnings False DoCmd.OpenQuery strDoc, acNormal, acEdit DoCmd.Close acForm, "frmSelectCriteriaCrosstab" DoCmd.SetWarnings True Exit_cmdOK_Click: Exit Sub Err_cmdOK_Click: If Err.Number = 3265 Then Resume Next Else MsgBox Err.Description Resume Exit_cmdOK_Click End If End Sub |
#2
|
|||
|
|||
Crosstab Query Building Report
A couple comments:
1) I generally use one function that accepts a listbox object, the field to query against, and the data type and returns a string like: " AND Color IN ('Red', 'Green','Blue') " I use this one function for all multi-select list boxes rather than repeating the same code over and over. 2) You don't need to delete and then recreate the querydef. Consider just setting the SQL property of an existing query. 3) There is a method for building a dynamic crosstab report at http://www.rogersaccesslibrary.com/f...sts.asp?TID=11 -- Duane Hookom Microsoft Access MVP "NEWER USER" wrote: I have a form with several multi list boxes. Once a row(s) is selected, a WHERE string is built, deletes an existing Query, and then builds a new Query1 with that Where string criteria. I then build a Crosstab query with the Query1 criteria. This works very well and vey fast. Instead of opening the Crosstab Query (as code shows), I would like to display the retrieved data in an AutoReport: Tabular design. Because the row values in my lstGroup box will change (required selection; have yet to code), I will need to delete/create/replace the existing report with the new values in the Crosstab Query because the Column Headings in the report must change with row selectionin lstGroup. Any ideas on how I might accomplish is gtreatly appreciated. Thanks. Private Sub cmdOK_Click() On Error GoTo Err_cmdOK_Click Dim varItem As Variant Dim strWhere As String Dim strWhere1 As String Dim strWhere2 As String Dim strWhere3 As String Dim strWhere4 As String Dim strWhere5 As String Dim strWhere6 As String Dim strWhere7 As String Dim strWhere8 As String Dim strWhere9 As String Dim lngLen As Long Dim strDelim As String Dim db As DAO.Database Dim qdf As DAO.QueryDef Dim strSQL As String Dim strDoc As String 'Build WHERE string With Me!lstGroup For Each varItem In .ItemsSelected If Not IsNull(varItem) Then strWhere1 = strWhere1 & strDelim & .ItemData(varItem) & strDelim & "," End If Next varItem End With lngLen = Len(strWhere1) - 1 If lngLen 0 Then strWhere1 = "[GroupID] IN (" & Left$(strWhere1, lngLen) & ") " End If With Me!lstClass For Each varItem In .ItemsSelected If Not IsNull(varItem) Then strWhere2 = strWhere2 & "'" & strDelim & .ItemData(varItem) & strDelim & "'," End If Next varItem End With lngLen = Len(strWhere2) - 1 If lngLen 0 Then strWhere2 = "[CO] IN (" & Left$(strWhere2, lngLen) & ") " End If With Me!lstBrand For Each varItem In .ItemsSelected If Not IsNull(varItem) Then strWhere3 = strWhere3 & strDelim & .ItemData(varItem) & strDelim & "," End If Next varItem End With lngLen = Len(strWhere3) - 1 If lngLen 0 Then strWhere3 = "[SupplierID] IN (" & Left$(strWhere3, lngLen) & ") " End If With Me!lstJGroup For Each varItem In .ItemsSelected If Not IsNull(varItem) Then strWhere4 = strWhere4 & "'" & strDelim & .ItemData(varItem) & strDelim & "'," End If Next varItem End With lngLen = Len(strWhere4) - 1 If lngLen 0 Then strWhere4 = "[JobGroup] IN (" & Left$(strWhere4, lngLen) & ") " End If With Me!lstMake For Each varItem In .ItemsSelected If Not IsNull(varItem) Then strWhere5 = strWhere5 & "'" & strDelim & .ItemData(varItem) & strDelim & "'," End If Next varItem End With lngLen = Len(strWhere5) - 1 If lngLen 0 Then strWhere5 = "[Make] IN (" & Left$(strWhere5, lngLen) & ") " End If With Me!lstModel For Each varItem In .ItemsSelected If Not IsNull(varItem) Then strWhere6 = strWhere6 & "'" & strDelim & .ItemData(varItem) & strDelim & "'," End If Next varItem End With lngLen = Len(strWhere6) - 1 If lngLen 0 Then strWhere6 = "[Model] IN (" & Left$(strWhere6, lngLen) & ") " End If With Me!lstEngine For Each varItem In .ItemsSelected If Not IsNull(varItem) Then strWhere7 = strWhere7 & "'" & strDelim & .ItemData(varItem) & strDelim & "'," End If Next varItem End With lngLen = Len(strWhere7) - 1 If lngLen 0 Then strWhere7 = "[Engine] IN (" & Left$(strWhere7, lngLen) & ") " End If With Me!lstType For Each varItem In .ItemsSelected If Not IsNull(varItem) Then strWhere8 = strWhere8 & strDelim & .ItemData(varItem) & strDelim & "," End If Next varItem End With lngLen = Len(strWhere8) - 1 If lngLen 0 Then strWhere8 = "[TypeID] IN (" & Left$(strWhere8, lngLen) & ") " End If With Me!lstEGroup For Each varItem In .ItemsSelected If Not IsNull(varItem) Then strWhere9 = strWhere9 & "'" & strDelim & .ItemData(varItem) & strDelim & "'," End If Next varItem End With lngLen = Len(strWhere9) - 1 If lngLen 0 Then strWhere9 = "[Description] IN (" & Left$(strWhere9, lngLen) & ") " End If strWhere = strWhere1 If Len(strWhere) 0 And Len(strWhere2) 0 Then strWhere = strWhere & " AND " & strWhere2 Else strWhere = strWhere & strWhere2 End If If Len(strWhere) 0 And Len(strWhere3) 0 Then strWhere = strWhere & " AND " & strWhere3 Else strWhere = strWhere & strWhere3 End If If Len(strWhere) 0 And Len(strWhere4) 0 Then strWhere = strWhere & " AND " & strWhere4 Else strWhere = strWhere & strWhere4 End If If Len(strWhere) 0 And Len(strWhere5) 0 Then strWhere = strWhere & " AND " & strWhere5 Else strWhere = strWhere & strWhere5 End If If Len(strWhere) 0 And Len(strWhere6) 0 Then strWhere = strWhere & " AND " & strWhere6 Else strWhere = strWhere & strWhere6 End If If Len(strWhere) 0 And Len(strWhere7) 0 Then strWhere = strWhere & " AND " & strWhere7 Else strWhere = strWhere & strWhere7 End If If Len(strWhere) 0 And Len(strWhere8) 0 Then strWhere = strWhere & " AND " & strWhere8 Else strWhere = strWhere & strWhere8 End If If Len(strWhere) 0 And Len(strWhere9) 0 Then strWhere = strWhere & " AND " & strWhere9 Else strWhere = strWhere & strWhere9 End If Set db = CurrentDb '*** create the query based on the information on the form strSQL = "SELECT qryxCatalog.* FROM qryxCatalog " strSQL = strSQL & " WHERE " & strWhere '*** delete the previous query db.QueryDefs.Delete "qryxCatalog1" Set qdf = db.CreateQueryDef("qryxCatalog1", strSQL) '*** open the query strDoc = "qryxCatalogCrosstab" DoCmd.SetWarnings False DoCmd.OpenQuery strDoc, acNormal, acEdit DoCmd.Close acForm, "frmSelectCriteriaCrosstab" DoCmd.SetWarnings True Exit_cmdOK_Click: Exit Sub Err_cmdOK_Click: If Err.Number = 3265 Then Resume Next Else MsgBox Err.Description Resume Exit_cmdOK_Click End If End Sub |
#3
|
|||
|
|||
Crosstab Query Building Report
Thanks for the input; I took a look at your Cross Tab.mdb for some examples.
I was able to build the report but ran into a snag with all null values in an entire column. I have a report that is based on a Crosstab query with three column headings .. The results of the crosstab query are based on selected rows from a multi list box. If less than three selections are made OR if three selections are made and one column contains No Values, the report fails and does not open. I get the message: "The Microsoft Jet database engine does not recognize " as a valid field name or expression." I open the report in design view and see the error message on the field that does not exist. There are over 2500 rows in my List Box and no way of knowing which contain/do not contain related records. How might I go about hiding the column heading/field when null values arise as the report opens? Any help appreciated. "Duane Hookom" wrote: A couple comments: 1) I generally use one function that accepts a listbox object, the field to query against, and the data type and returns a string like: " AND Color IN ('Red', 'Green','Blue') " I use this one function for all multi-select list boxes rather than repeating the same code over and over. 2) You don't need to delete and then recreate the querydef. Consider just setting the SQL property of an existing query. 3) There is a method for building a dynamic crosstab report at http://www.rogersaccesslibrary.com/f...sts.asp?TID=11 -- Duane Hookom Microsoft Access MVP "NEWER USER" wrote: I have a form with several multi list boxes. Once a row(s) is selected, a WHERE string is built, deletes an existing Query, and then builds a new Query1 with that Where string criteria. I then build a Crosstab query with the Query1 criteria. This works very well and vey fast. Instead of opening the Crosstab Query (as code shows), I would like to display the retrieved data in an AutoReport: Tabular design. Because the row values in my lstGroup box will change (required selection; have yet to code), I will need to delete/create/replace the existing report with the new values in the Crosstab Query because the Column Headings in the report must change with row selectionin lstGroup. Any ideas on how I might accomplish is gtreatly appreciated. Thanks. Private Sub cmdOK_Click() On Error GoTo Err_cmdOK_Click Dim varItem As Variant Dim strWhere As String Dim strWhere1 As String Dim strWhere2 As String Dim strWhere3 As String Dim strWhere4 As String Dim strWhere5 As String Dim strWhere6 As String Dim strWhere7 As String Dim strWhere8 As String Dim strWhere9 As String Dim lngLen As Long Dim strDelim As String Dim db As DAO.Database Dim qdf As DAO.QueryDef Dim strSQL As String Dim strDoc As String 'Build WHERE string With Me!lstGroup For Each varItem In .ItemsSelected If Not IsNull(varItem) Then strWhere1 = strWhere1 & strDelim & .ItemData(varItem) & strDelim & "," End If Next varItem End With lngLen = Len(strWhere1) - 1 If lngLen 0 Then strWhere1 = "[GroupID] IN (" & Left$(strWhere1, lngLen) & ") " End If With Me!lstClass For Each varItem In .ItemsSelected If Not IsNull(varItem) Then strWhere2 = strWhere2 & "'" & strDelim & .ItemData(varItem) & strDelim & "'," End If Next varItem End With lngLen = Len(strWhere2) - 1 If lngLen 0 Then strWhere2 = "[CO] IN (" & Left$(strWhere2, lngLen) & ") " End If With Me!lstBrand For Each varItem In .ItemsSelected If Not IsNull(varItem) Then strWhere3 = strWhere3 & strDelim & .ItemData(varItem) & strDelim & "," End If Next varItem End With lngLen = Len(strWhere3) - 1 If lngLen 0 Then strWhere3 = "[SupplierID] IN (" & Left$(strWhere3, lngLen) & ") " End If With Me!lstJGroup For Each varItem In .ItemsSelected If Not IsNull(varItem) Then strWhere4 = strWhere4 & "'" & strDelim & .ItemData(varItem) & strDelim & "'," End If Next varItem End With lngLen = Len(strWhere4) - 1 If lngLen 0 Then strWhere4 = "[JobGroup] IN (" & Left$(strWhere4, lngLen) & ") " End If With Me!lstMake For Each varItem In .ItemsSelected If Not IsNull(varItem) Then strWhere5 = strWhere5 & "'" & strDelim & .ItemData(varItem) & strDelim & "'," End If Next varItem End With lngLen = Len(strWhere5) - 1 If lngLen 0 Then strWhere5 = "[Make] IN (" & Left$(strWhere5, lngLen) & ") " End If With Me!lstModel For Each varItem In .ItemsSelected If Not IsNull(varItem) Then strWhere6 = strWhere6 & "'" & strDelim & .ItemData(varItem) & strDelim & "'," End If Next varItem End With lngLen = Len(strWhere6) - 1 If lngLen 0 Then strWhere6 = "[Model] IN (" & Left$(strWhere6, lngLen) & ") " End If With Me!lstEngine For Each varItem In .ItemsSelected If Not IsNull(varItem) Then strWhere7 = strWhere7 & "'" & strDelim & .ItemData(varItem) & strDelim & "'," End If Next varItem End With lngLen = Len(strWhere7) - 1 If lngLen 0 Then strWhere7 = "[Engine] IN (" & Left$(strWhere7, lngLen) & ") " End If With Me!lstType For Each varItem In .ItemsSelected If Not IsNull(varItem) Then strWhere8 = strWhere8 & strDelim & .ItemData(varItem) & strDelim & "," End If Next varItem End With lngLen = Len(strWhere8) - 1 If lngLen 0 Then strWhere8 = "[TypeID] IN (" & Left$(strWhere8, lngLen) & ") " End If With Me!lstEGroup For Each varItem In .ItemsSelected If Not IsNull(varItem) Then strWhere9 = strWhere9 & "'" & strDelim & .ItemData(varItem) & strDelim & "'," End If Next varItem End With lngLen = Len(strWhere9) - 1 If lngLen 0 Then strWhere9 = "[Description] IN (" & Left$(strWhere9, lngLen) & ") " End If strWhere = strWhere1 If Len(strWhere) 0 And Len(strWhere2) 0 Then strWhere = strWhere & " AND " & strWhere2 Else strWhere = strWhere & strWhere2 End If If Len(strWhere) 0 And Len(strWhere3) 0 Then strWhere = strWhere & " AND " & strWhere3 Else strWhere = strWhere & strWhere3 End If If Len(strWhere) 0 And Len(strWhere4) 0 Then strWhere = strWhere & " AND " & strWhere4 Else strWhere = strWhere & strWhere4 End If If Len(strWhere) 0 And Len(strWhere5) 0 Then strWhere = strWhere & " AND " & strWhere5 Else strWhere = strWhere & strWhere5 End If If Len(strWhere) 0 And Len(strWhere6) 0 Then strWhere = strWhere & " AND " & strWhere6 Else strWhere = strWhere & strWhere6 End If If Len(strWhere) 0 And Len(strWhere7) 0 Then strWhere = strWhere & " AND " & strWhere7 Else strWhere = strWhere & strWhere7 End If If Len(strWhere) 0 And Len(strWhere8) 0 Then strWhere = strWhere & " AND " & strWhere8 Else strWhere = strWhere & strWhere8 End If If Len(strWhere) 0 And Len(strWhere9) 0 Then strWhere = strWhere & " AND " & strWhere9 Else strWhere = strWhere & strWhere9 End If Set db = CurrentDb '*** create the query based on the information on the form strSQL = "SELECT qryxCatalog.* FROM qryxCatalog " strSQL = strSQL & " WHERE " & strWhere '*** delete the previous query db.QueryDefs.Delete "qryxCatalog1" Set qdf = db.CreateQueryDef("qryxCatalog1", strSQL) '*** open the query strDoc = "qryxCatalogCrosstab" DoCmd.SetWarnings False DoCmd.OpenQuery strDoc, acNormal, acEdit DoCmd.Close acForm, "frmSelectCriteriaCrosstab" DoCmd.SetWarnings True Exit_cmdOK_Click: Exit Sub Err_cmdOK_Click: If Err.Number = 3265 Then Resume Next Else MsgBox Err.Description Resume Exit_cmdOK_Click End If End Sub |
Thread Tools | |
Display Modes | |
|
|