Here's an example that's working for me:
'Declare variables for SQL query
Dim strSQL As String
Dim strTable As String
Dim strStat As String
Dim strGroup As String
Dim strTransform As String
Dim strPivot As String
Dim strDate As String
'Set variables for SQL query
If StatCombo.Value = "SNBE" Then
strTable = "TPERF_STATS_DAILY_CHT"
Else
strTable = "TPERF_STATS_DAILY_CC"
End If
strStat = StatCombo.Value
strGroup = GroupByCombo.Value
If StatCombo.Value = "CALLS_HANDLED" Then
strTransform = "Sum"
Else
strTransform = "Avg"
End If
strPivot = "NOM_DATE"
strDate = "NOM_DATE"
'Construct SQL query
strSQL = "PARAMETERS [Forms]![SelectDate]![StartDate] DateTime,
[Forms]![SelectDate]![EndDate] DateTime; " & _
"TRANSFORM " & strTransform & "(" & strTable & ".[" & strStat & "]) AS
[" & strTransform & "Of" & strStat & "] " & _
"SELECT " & strTable & ".[" & strGroup & "] " & _
"FROM " & strTable & " " & _
"WHERE (((" & strTable & "." & strDate & ") Between
[Forms]![SelectDate]![StartDate] And [Forms]![SelectDate]![EndDate])) " & _
"GROUP BY " & strTable & ".[" & strGroup & "] " & _
"PIVOT " & strTable & "." & strPivot & "; "
'Pass SQL query to Query1
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("Query1")
qdf.SQL = strSQL
Obviously you can create the various strings from which the query is built
however you want.
Hope this helps.
