I have the same problem with crosstabs this is my first in years
thanks for your help I'll try the db guys solution again with my rota screen be back soon
Added the peramators both the way thedbguy and Isladogs suggested
Code:
PARAMETERS Forms!frmRota.form.txtFrom DateTime, Forms!frmRota.form.txtTo DateTime;
TRANSFORM First(QryJobCrosstabData.Times) AS FirstOfTimes
SELECT QryJobCrosstabData.Employee
FROM QryJobCrosstabData
GROUP BY QryJobCrosstabData.Employee
ORDER BY Format([AssignedDate],"Short Date")
PIVOT Format([AssignedDate],"Short Date");
I still get a blank subform but it opens when I open the crosstab from the db window but it still shows all records
Actually that was the DB Guy's suggestion.
AFAIA I have never used parameters with a crosstab query.
Some forum members state they are required in any crosstab query. I disagree.
Added the peramators both the way thedbguy and Isladogs suggested
Code:
PARAMETERS Forms!frmRota.form.txtFrom DateTime, Forms!frmRota.form.txtTo DateTime;
TRANSFORM First(QryJobCrosstabData.Times) AS FirstOfTimes
SELECT QryJobCrosstabData.Employee
FROM QryJobCrosstabData
GROUP BY QryJobCrosstabData.Employee
ORDER BY Format([AssignedDate],"Short Date")
PIVOT Format([AssignedDate],"Short Date");
I still get a blank subform but it opens when I open the crosstab from the db window but it still shows all records
If it's opened without the form then it displays all data I had a look at your one could see where you made the change but as you said it mucks the date data up.
I used an actual form for the crosstab, not just a subform control.
Each of the controls is named vName and a digit, eg vName1, vName2
The form has no record source, that is created on Form open
Code:
Private Sub Form_Load()
Dim n As Integer
Dim fld As Variant
For Each fld In Me.Recordset.Fields
Do
With Me.Controls(n)
If .ControlType = acTextBox And Left(.Name, 5) = "vName" Then
.ControlSource = fld.Name
.Controls(0).Caption = fld.Name
n = n + 1
Exit Do
End If
End With
n = n + 1
Loop
Next
End Sub
Public Sub Form_Open(Cancel As Integer)
Dim strCaller As String, strSql As String, strQuery As String
strCaller = PersonList()
strQuery = "qryWeekCallSummary_Crosstab"
'strSQL = "PARAMETERS pCallSystem Text ( 255 );"
strSql = strSql & " TRANSFORM Sum([SumOfCallSecs]/86400) AS Duration"
strSql = strSql & " SELECT qryWeekCallSummary.WeekEndDate"
strSql = strSql & " FROM qryWeekCallSummary "
If Len(gCallSystem) > 0 Then
strSql = strSql & " WHERE qryWeekCallSummary.CallSystem = '" & gCallSystem & "'"
End If
strSql = strSql & " GROUP BY qryWeekCallSummary.WeekEndDate"
strSql = strSql & " ORDER BY qryWeekCallSummary.WeekEndDate DESC"
strSql = strSql & " PIVOT qryWeekCallSummary.Caller IN (" & strCaller & ")"
'CurrentDb.QueryDefs(strQuery).Parameters("[pCallSystem]") = gCallSystem
CurrentDb.QueryDefs(strQuery).SQL = strSql
Me.RecordSource = strSql
End Sub
PersonList was just getting active users. In your case it would be your dates.
gCallSystem was either BT or the other one which I cannot remember now.:banghead:
So in fact mine is the opposite to yours as I have limited users and lots of dates, but that is a reasonably simple method to have dynamic crosstab headings.