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

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
> 
> .
> 

On Thu, 22 Apr 2010 12:54:26 GMT, "slprescott via AccessMonster.com"
<u59529@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]

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

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.
> 

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

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.
> 

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

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
> 

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