Filtering a Crosstab Query from Combo Boxes on Unbound Form
in [Queries]
|
From: slprescott via AccessMonster.com on 22 Apr 2010 08:54 Hello Everyone, I have a crosstab query in my database that I would like to filter based upon user selections from combo boxes on an unbound form. I would like for the users to be able to make a selection from four combo boxes (cboSelectWeek, cboSelectMonth, cboSelectGroup and cboSelectFocal) and then push a button to see a crosstab query reflecting their selections; I would like for the query to export to excel and be saved as a file of the user's choosing. Also, the user should be able to leave some of the combo boxes null. I have been reading on this site (which has taught me a lot over the last few months) for a solution, but haven't been able to get anything to work for me. I am using some code from Allen Browne's website regarding building a filter string, along with other bits and pieces. I think I have just managed to confuse myself!!! I am still new to Access, SQL and VBA, so a basic answer would be appreciated - but I'll take any help I can get!!!! Thanks in advance. Here is the code I am currently trying (but it gives me a error saying there are problems with the "TRANSFORM" statement): Private Sub cmdProjectHoursWeekly_Click() Dim cnnX As ADODB.Connection Set cnnX = CurrentProject.Connection Dim myrecordset As New ADODB.Recordset myrecordset.ActiveConnection = cnnX Dim strWhere As String Dim strcStub As String Dim strcTail As String strcStub = "TRANSFORM Sum(WORK_TBL.Hours) AS Hrs" strcStub = strcStub + " SELECT WORKPACKAGE_TBL.WPID, WORKPACKAGE_TBL. WP_Title,WORKPACKAGE_TBL.TeamName, WORKPACKAGE_TBL.ANAEMFocal" strcStub = strcStub + " FROM DB_Calendar (INNER JOIN WORK_TBL (INNER JOIN USER_TBL ON WORK_TBL.User = USER_TBL.User) (INNER JOIN WORKPACKAGE_TBL ON WORK_TBL.WPID = WORKPACKAGE_TBL.WPID) ON DB_Calendar.DATE = WORK_TBL.Workdate) " strcStub = strcStub + " WHERE ((DB_Calendar.Year) > ((Format(Date, 'yyyy')) - 1)) And ((WORK_TBL.WPID) Is Not Null)) And " strcTail = " GROUP BY WORKPACKAGE_TBL.WPID, WORKPACKAGE_TBL.WP_Title, WORKPACKAGE_TBL.TeamName, DB_Calendar.YEAR, WORKPACKAGE_TBL.ANAEMFocal" strcTail = strcTail + " ORDER BY WORKPACKAGE_TBL.WPID, DB_Calendar.WEEK" strcTail = strcTail + " PIVOT DB_Calendar.WEEK" 'Build the filter string. If Not IsNull(Me.cboSelectWeek) Then strWhere = strWhere & "([WEEK] = " & Me.cboSelectWeek.Value & ") AND " End If If Not IsNull(Me.cboSelectFocal) Then strWhere = strWhere & "([ANAEMFocal] = """ & Me.cboSelectFocal.Value & """) AND " End If If Not IsNull(Me.cboSelectTeam) Then strWhere = strWhere & "([TeamName] = """ & Me.cboSelectTeam.Value & """) AND " End If lngLen = Len(strWhere) - 5 If lngLen <= 0 Then MsgBox "No criteria", vbInformation, "Nothing to do." Else strWhere = Left$(strWhere, lngLen) Dim mySQL As String mySQL = strcStub & strWhere & strcTail Dim strFile As String strFile = "S:\Temp\MyFile.xls" myrecordset.Open mySQL DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, myrecordset, strFile, True End If End Sub -- Message posted via http://www.accessmonster.com
From: Jerry Whittle on 22 Apr 2010 10:40 First off create a select query and use the combo boxes for criteria in it. Save it. Then use this query as the record source for your crosstab query. Personally I'd also create the crosstab query the normal way rather than put it in code. You can later run the query in code if need be. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "slprescott via AccessMonster.com" wrote: > Hello Everyone, > > I have a crosstab query in my database that I would like to filter based upon > user selections from combo boxes on an unbound form. I would like for the > users to be able to make a selection from four combo boxes (cboSelectWeek, > cboSelectMonth, cboSelectGroup and cboSelectFocal) and then push a button to > see a crosstab query reflecting their selections; I would like for the query > to export to excel and be saved as a file of the user's choosing. Also, the > user should be able to leave some of the combo boxes null. > > I have been reading on this site (which has taught me a lot over the last few > months) for a solution, but haven't been able to get anything to work for me. > I am using some code from Allen Browne's website regarding building a filter > string, along with other bits and pieces. > > I think I have just managed to confuse myself!!! I am still new to Access, > SQL and VBA, so a basic answer would be appreciated - but I'll take any help > I can get!!!! > > Thanks in advance. > > Here is the code I am currently trying (but it gives me a error saying there > are problems with the "TRANSFORM" statement): > > Private Sub cmdProjectHoursWeekly_Click() > Dim cnnX As ADODB.Connection > Set cnnX = CurrentProject.Connection > Dim myrecordset As New ADODB.Recordset > myrecordset.ActiveConnection = cnnX > > > Dim strWhere As String > Dim strcStub As String > Dim strcTail As String > > strcStub = "TRANSFORM Sum(WORK_TBL.Hours) AS Hrs" > strcStub = strcStub + " SELECT WORKPACKAGE_TBL.WPID, WORKPACKAGE_TBL. > WP_Title,WORKPACKAGE_TBL.TeamName, WORKPACKAGE_TBL.ANAEMFocal" > strcStub = strcStub + " FROM DB_Calendar (INNER JOIN WORK_TBL (INNER JOIN > USER_TBL ON WORK_TBL.User = USER_TBL.User) (INNER JOIN WORKPACKAGE_TBL ON > WORK_TBL.WPID = WORKPACKAGE_TBL.WPID) ON DB_Calendar.DATE = WORK_TBL.Workdate) > " > strcStub = strcStub + " WHERE ((DB_Calendar.Year) > ((Format(Date, 'yyyy')) - > 1)) And ((WORK_TBL.WPID) Is Not Null)) And " > > strcTail = " GROUP BY WORKPACKAGE_TBL.WPID, WORKPACKAGE_TBL.WP_Title, > WORKPACKAGE_TBL.TeamName, DB_Calendar.YEAR, WORKPACKAGE_TBL.ANAEMFocal" > strcTail = strcTail + " ORDER BY WORKPACKAGE_TBL.WPID, DB_Calendar.WEEK" > strcTail = strcTail + " PIVOT DB_Calendar.WEEK" > > 'Build the filter string. > If Not IsNull(Me.cboSelectWeek) Then > strWhere = strWhere & "([WEEK] = " & Me.cboSelectWeek.Value & ") AND > " > End If > > If Not IsNull(Me.cboSelectFocal) Then > strWhere = strWhere & "([ANAEMFocal] = """ & Me.cboSelectFocal.Value > & """) AND " > End If > > If Not IsNull(Me.cboSelectTeam) Then > strWhere = strWhere & "([TeamName] = """ & Me.cboSelectTeam.Value & > """) AND " > End If > > > lngLen = Len(strWhere) - 5 > If lngLen <= 0 Then > MsgBox "No criteria", vbInformation, "Nothing to do." > Else > strWhere = Left$(strWhere, lngLen) > > Dim mySQL As String > mySQL = strcStub & strWhere & strcTail > > Dim strFile As String > strFile = "S:\Temp\MyFile.xls" > > myrecordset.Open mySQL > DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, myrecordset, > strFile, True > End If > > End Sub > > -- > Message posted via http://www.accessmonster.com > > . >
From: John W. Vinson on 22 Apr 2010 11:45 On Thu, 22 Apr 2010 12:54:26 GMT, "slprescott via AccessMonster.com" <u59529(a)uwe> wrote: >I have a crosstab query in my database that I would like to filter based upon >user selections from combo boxes on an unbound form. I would like for the >users to be able to make a selection from four combo boxes (cboSelectWeek, >cboSelectMonth, cboSelectGroup and cboSelectFocal) and then push a button to >see a crosstab query reflecting their selections; I would like for the query >to export to excel and be saved as a file of the user's choosing. Also, the >user should be able to leave some of the combo boxes null. In any query you can use the Parameters property to specify the query's parameters: e.g. in SQL putting PARAMETERS [Forms]![frmCriteria]![cboSelectWeek] Integer; With Crosstab queries for some reason this specification is *required*. Try adding it to your SQL string. -- John W. Vinson [MVP]
From: slprescott via AccessMonster.com on 22 Apr 2010 12:27 John, Thanks so much for your response. I have learned a lot from reading your posts over the last few months. I tried you suggestion and added the parameters above my transform statement. Dim strWhere As String Dim strcStub As String Dim strcTail As String strcStub = "PARAMETERS [Forms].[frmOpElementReports].[cboSelectWeek] IEEEDouble, [Forms].[frmOpElementReports].[cboSelectMonth] IEEEDouble, [Forms] .[frmOpElementReports].[cboSelectTeam] Text(255), [Forms]. [frmOpElementReports].[cboSelectFocal] Text(255)" strcStub = "TRANSFORM Sum(WORK_TBL.Hours) AS Hrs" Unfortunately, I am still getting the error regarding the Transform statement . . . Thanks again! John W. Vinson wrote: >>I have a crosstab query in my database that I would like to filter based upon >>user selections from combo boxes on an unbound form. I would like for the >[quoted text clipped - 3 lines] >>to export to excel and be saved as a file of the user's choosing. Also, the >>user should be able to leave some of the combo boxes null. > >In any query you can use the Parameters property to specify the query's >parameters: e.g. in SQL putting > >PARAMETERS [Forms]![frmCriteria]![cboSelectWeek] Integer; > >With Crosstab queries for some reason this specification is *required*. Try >adding it to your SQL string. -- Message posted via http://www.accessmonster.com
From: John Spencer on 22 Apr 2010 13:12
First, you need to ADD the parameter declaration to the query string. Second, it must be terminated with a semi-colon Third, Use ! to delimit the segments of the control references strcStub = "PARAMETERS [Forms]![frmOpElementReports]![cboSelectWeek] IEEEDouble , [Forms]![frmOpElementReports]![cboSelectMonth] IEEEDouble , [Forms]![frmOpElementReports]![cboSelectTeam] Text(255) , [Forms]![frmOpElementReports]![cboSelectFocal] Text(255) ;" & vbcrlf strcStub = strcStub & "TRANSFORM Sum(WORK_TBL.Hours) AS Hrs" John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County slprescott via AccessMonster.com wrote: > John, > > Thanks so much for your response. I have learned a lot from reading your > posts over the last few months. > > I tried you suggestion and added the parameters above my transform statement. > > > Dim strWhere As String > Dim strcStub As String > Dim strcTail As String > > strcStub = "PARAMETERS [Forms].[frmOpElementReports].[cboSelectWeek] > IEEEDouble, [Forms].[frmOpElementReports].[cboSelectMonth] IEEEDouble, [Forms] > .[frmOpElementReports].[cboSelectTeam] Text(255), [Forms]. > [frmOpElementReports].[cboSelectFocal] Text(255)" > strcStub = "TRANSFORM Sum(WORK_TBL.Hours) AS Hrs" > > Unfortunately, I am still getting the error regarding the Transform statement > . . . > > Thanks again! > > John W. Vinson wrote: >>> I have a crosstab query in my database that I would like to filter based upon >>> user selections from combo boxes on an unbound form. I would like for the >> [quoted text clipped - 3 lines] >>> to export to excel and be saved as a file of the user's choosing. Also, the >>> user should be able to leave some of the combo boxes null. >> In any query you can use the Parameters property to specify the query's >> parameters: e.g. in SQL putting >> >> PARAMETERS [Forms]![frmCriteria]![cboSelectWeek] Integer; >> >> With Crosstab queries for some reason this specification is *required*. Try >> adding it to your SQL string. > |