Dynamically Building Crosstab Queries/Forms

[Guest]

I am trying to find if there is a way to dynamically define a Record Source,
on a form, using a Crosstab structure.

If I use:

TRANSFORM Sum(tblTimeEntries.Hours) AS SumOfHours
SELECT tblTimeEntries.TimeTypeID, tblTimeEntries.ServiceCall
FROM tblTimeEntries
GROUP BY tblTimeEntries.TimeTypeID, tblTimeEntries.ServiceCall
PIVOT tblTimeEntries.Date In (1/23/2006, 1/24/2006, 1/25/2006, 1/26/2006,
1/27/2006, 1/28/2006, 1/29/2006);

The query/form generate correctly. However if I modify the PIVOT line to:

PIVOT tblTimeEntries.Date In ([forms]![frmTimeSheet]![cmbDay1],
[forms]![frmTimeSheet]![cmbDay2], [forms]![frmTimeSheet]![cmbDay3],
[forms]![frmTimeSheet]![cmbDay4], [forms]![frmTimeSheet]![cmbDay5],
[forms]![frmTimeSheet]![cmbDay6], [forms]![frmTimeSheet]![cmbDay7]);

or use a simple +1, +2, +3, etc. calculation, I get a Data Type Mismatch
error.

I have tried using the DateValue function, with no luck. The additional
'('s create a Missing ], (, ) error.

Any suggestions, or is this even possible?

Thank you, in advance.

Sharkbyte

 

[Allen Browne]

You can build the SQL statement dynamically like this:

Dim strSql As String
Dim lngLen As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"
Const strcSep = ","

If IsDate(Me.cmbDay1) Then
strSql = strSql & Format(Me.cmbDay1, strcJetDate) & strcSep
End If

If IsDate(Me.cmbDay2) Then
strSql = strSql & Format(Me.cmbDay2, strcJetDate) & strcSep
End If

'etc for other combos.

'Chop off the trailing separator
lngLen = Len(strSql) - Len(strcSep)
If lngLen > 0 Then
strSql = strSql & "TRANSFORM Sum(tblTimeEntries.Hours) AS SumOfHours " &
_
"SELECT tblTimeEntries.TimeTypeID, tblTimeEntries.ServiceCall " & _
"FROM tblTimeEntries " & _
"GROUP BY tblTimeEntries.TimeTypeID, tblTimeEntries.ServiceCall " & _
"PIVOT tblTimeEntries.Date IN (" & Left$(strSql, lngLen) & ");"
Me.RecordSource = strSql
Else
MsgBox "Go pick some dates."
End If