From: slprescott via AccessMonster.com on
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
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
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
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
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.
>