Filtering a Crosstab Query from Combo Boxes on Unbound Form
in [Queries]
|
From: slprescott via AccessMonster.com on 22 Apr 2010 13:55 John, Thanks. I have also learned a lot from your contributions as well. Thanks so much for everyone's help on this; I really do appreciate ti. I amended my SQL to reflect the changes: > >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" > I am still getting the error message. Specifically, the error is: "Run-time error '-2147217900(80040e14)': Syntax error in TRANSFORM statement. " Also, when I click Debug, the line of code that highlights yellow is: myrecordset.Open mySQL Thanks again for all the advice. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201004/1
From: John Spencer on 22 Apr 2010 15:22 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 = "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" 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) " strcTail = VBCRLF & " 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" If DB_Calendar.Year is a number field then strWhere= " DB_Calendar.Year > Year(Date()) -1 And WORK_TBL.WPID Is Not Null" If DB_Calendar.Yearis a string field then strWhere= " DB_Calendar.Year > """" & Year(Date()) -1 And WORK_TBL.WPID Is Not Null" 'Build the filter string. If Not IsNull(Me.cboSelectWeek) Then strWhere = strWhere & " AND [WEEK] = " & Me.cboSelectWeek 'If Week is not a number field but a text field then add in the quotes strWhere = strWhere & " AND [WEEK] = '" & Me.cboSelectWeek & "'" End If If Not IsNull(Me.cboSelectFocal) Then strWhere = strWhere & " AND [ANAEMFocal] = """ & Me.cboSelectFocal & """" End If If Not IsNull(Me.cboSelectTeam) Then strWhere = strWhere & " AND [TeamName] = """ & Me.cboSelectTeam & """" End If Dim mySQL As String mySQL = strcStub & " WHERE & strWhere & strcTail 'Add the following lines so you can debug the SQL statement Debug.Print mySQL STOP 'You can copy the SQL string from the immediate window 'and paste it into a blank query. Then try to run it and see what errors 'occur. Troubleshoot the query - decide what you need to fix in the code. Dim strFile As String strFile = "S:\Temp\MyFile.xls" John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County slprescott via AccessMonster.com wrote: > John, > > Thanks. I have also learned a lot from your contributions as well. > > Thanks so much for everyone's help on this; I really do appreciate ti. > > I amended my SQL to reflect the changes: >> 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" >> > > I am still getting the error message. Specifically, the error is: > > "Run-time error '-2147217900(80040e14)': Syntax error in TRANSFORM statement. > " > > Also, when I click Debug, the line of code that highlights yellow is: > > myrecordset.Open mySQL > > Thanks again for all the advice. >
From: slprescott via AccessMonster.com on 23 Apr 2010 11:24 John, This is excellent! I got the code to work for the SQL statement! Thanks so much!!!! Of course, one problem solved, then another one crops up. I just can't seem to get the crazy thing into an exel sheet! When I add the code to open the recordset and send to exel, I get an error that one or more required parameters is missing (this happens if all or some of the combo boxes are not filled out OR if all the combo boxes are filled out). This is not a problem when I debug and paste into an empty query for testing - it runs perfectly in the testing query SQL window. Any ideas??? I could not have done this without all the help from here. Thanks again! The code I ended up with is this: 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 = "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" 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 " strcTail = vbCrLf & " 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" strWhere = " (((DB_Calendar.YEAR)>((Year(Date()))-1)) AND ((WORK_TBL.WPID) Is Not Null))" 'Build the filter string. If Not IsNull(Me.cboSelectWeek) Then strWhere = strWhere & " AND DB_Calendar.[WEEK] = " & Me.cboSelectWeek End If If Not IsNull(Me.cboSelectFocal) Then strWhere = strWhere & " AND [ANAEMFocal] = """ & Me.cboSelectFocal & """" End If If Not IsNull(Me.cboSelectTeam) Then strWhere = strWhere & " AND [TeamName] = """ & Me.cboSelectTeam & """" End If Dim mySQL As String mySQL = strcStub & "WHERE" & strWhere & strcTail Debug.Print mySQL Dim strFile As String strFile = "S:\Temp\MyFile.xls" myrecordset.Open mySQL DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, myrecordset, strFile, True End Sub -- Message posted via http://www.accessmonster.com
From: John Spencer on 23 Apr 2010 12:18 As far as I know you have to use a table name or a stored query name to use the TransferSpreadsheet method. So you could create a querydef, use the SQL string to assign that to the querydef's SQL property and then save the querydef. THEN you could use the name of the querydef in the TransferSpreadsheet method. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County slprescott via AccessMonster.com wrote: > John, > > This is excellent! I got the code to work for the SQL statement! Thanks so > much!!!! > > Of course, one problem solved, then another one crops up. I just can't seem > to get the crazy thing into an exel sheet! > > When I add the code to open the recordset and send to exel, I get an error > that one or more required parameters is missing (this happens if all or some > of the combo boxes are not filled out OR if all the combo boxes are filled > out). This is not a problem when I debug and paste into an empty query for > testing - it runs perfectly in the testing query SQL window. > > Any ideas??? > > I could not have done this without all the help from here. Thanks again! > > The code I ended up with is this: > > 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 = "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" > 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 > " > > strcTail = vbCrLf & " 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" > > > strWhere = " (((DB_Calendar.YEAR)>((Year(Date()))-1)) AND ((WORK_TBL.WPID) > Is Not Null))" > > > > 'Build the filter string. > If Not IsNull(Me.cboSelectWeek) Then > strWhere = strWhere & " AND DB_Calendar.[WEEK] = " & Me.cboSelectWeek > End If > > If Not IsNull(Me.cboSelectFocal) Then > strWhere = strWhere & " AND [ANAEMFocal] = """ & Me.cboSelectFocal & """" > End If > > If Not IsNull(Me.cboSelectTeam) Then > strWhere = strWhere & " AND [TeamName] = """ & Me.cboSelectTeam & """" > End If > > > Dim mySQL As String > mySQL = strcStub & "WHERE" & strWhere & strcTail > > Debug.Print mySQL > > > Dim strFile As String > strFile = "S:\Temp\MyFile.xls" > > > myrecordset.Open mySQL > DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, myrecordset, > strFile, True > > > > End Sub >
From: slprescott via AccessMonster.com on 26 Apr 2010 14:56
John, Thanks very much for all of your excellent help. That is what I was thinking I would need to do, but I was not sure. I am finally finished (with this piece of the puzzle, anyway), and it works beautifully! I ended up giving up on the TransferSpreadsheet method and just cheated, using a macro to export the query results so that the user can save the file as he/she wishes. I could not have done this without your help. And thanks to everyone else on this blog; I have used many other posts to help fill in code and build my base knowledge - small though it is!!!! - of Access. For anyone else who is interested, here is the resulting code, which allows the user to filter a query on combo boxes (some of which can be null) from an unbound form and export the query results to excel: 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 = "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" 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 " strcTail = vbCrLf & " 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" strWhere = " (((DB_Calendar.YEAR)>((Year(Date()))-1)) AND ((WORK_TBL.WPID) Is Not Null))" 'Build the filter string. If Not IsNull(Me.cboSelectWeek) Then strWhere = strWhere & " AND DB_Calendar.[WEEK] = " & Me.cboSelectWeek End If If Not IsNull(Me.cboSelectFocal) Then strWhere = strWhere & " AND [ANAEMFocal] = """ & Me.cboSelectFocal & """" End If If Not IsNull(Me.cboSelectTeam) Then strWhere = strWhere & " AND [TeamName] = """ & Me.cboSelectTeam & """" End If Dim mySQL As String mySQL = strcStub & "WHERE" & strWhere & strcTail 'Add the following lines so you can debug the SQL statement Debug.Print mySQL 'Stop 'You can copy the SQL string from the immediate window 'and paste it into a blank query. Then try to run it and see what errors 'occur. Troubleshoot the query - decide what you need to fix in the code. Dim strFile As String strFile = "mcrExport_ProjectHours_Weekly" Dim strSQL As String Dim strQryName As String 'name of your stored query strQryName = "qryProjectHours_Weekly" 'create new SQL for your stored query strSQL = mySQL 'redefine query Set qdf = CurrentDb.QueryDefs(strQryName) qdf.SQL = strSQL qdf.Close DoCmd.RunMacro strFile End Sub -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201004/1 |