Filtering a Crosstab Query from Combo Boxes on Unbound Form

[slprescott via AccessMonster.com]

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

 

[Jerry Whittle]

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.

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

 

[John W. Vinson]

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.

 

[slprescott via AccessMonster.com]

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!

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.

 

[John Spencer]

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

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!

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.

 

[slprescott via AccessMonster.com]

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.

 

[John Spencer]

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

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.

 

[slprescott via AccessMonster.com]

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

 

[John Spencer]

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

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

 

[slprescott via AccessMonster.com]

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