Crosstab Report w/Changing Dates

[mtorr2x]

I have been tasked to create a db that has employees, charge numbers, and
hours worked per week. i am trying to generate a report that displays up to
5 weeks of summary data based on the work ending date.

I found a thread posted about a year ago called "dynamic crosstab A2002". I
have used this thread to create my report but i get a blank report. If I
ener in the absolute date in the Criteria of the crosstab query the report
does work as expected. But if i enter in
Between ([forms]![frm_NameData].[cbo_WEStartDate].[value]) And ([forms]!
[frm_NameData].[cbo_WEEndDate].[value])
I get the blank report.

i looked at the created query based off of the crosstab query and the SQL is:

SELECT Null AS Field0, Null AS Field1, Null AS Field2, Null AS Field3, Null
AS Field4, Null AS Field5, Null AS Field6, Null AS Field7, Null AS Field8,
Null AS Field9
FROM Crosstab_Name2;

Why am i getting Null values?


My code for the report is:

Option Compare Database
Option Explicit
Dim reportlabel(10) As String
_______________________________________________
Private Sub Report_Open(Cancel As Integer)

'DoCmd.Maximize
Dim i As Integer
For i = 0 To 9
reportlabel(i) = ""
Next i
Call CreateReportQuery
End Sub
__________________________________________

Sub CreateReportQuery()
On Error GoTo Err_CreateQuery

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim indexx As Integer
Dim FieldList As String
Dim strSQL As String
Dim i As Integer
Dim j As Field

Set db = CurrentDb
Set qdf = db.QueryDefs("Crosstab_Name2")
indexx = 0

For Each fld In qdf.Fields
If fld.Type >= 1 And fld.Type <= 8 Or fld.Type = 10 Then
FieldList = FieldList & "[" & fld.name & "] as Field" & indexx &
", "
reportlabel(indexx) = fld.name
End If

indexx = indexx + 1
Next fld
For i = indexx To 9
FieldList = FieldList & "null as Field" & i & ","
Next i
FieldList = Left(FieldList, Len(FieldList) - 1)

strSQL = "Select " & FieldList & " From Crosstab_Name2" & ";"
'MsgBox strSQL

db.QueryDefs.Delete "Crosstab_Report_Name2"
Set qdf = db.CreateQueryDef("Crosstab_Report_Name2", strSQL)

Exit_CreateQuery:
Exit Sub

Err_CreateQuery:
If Err.Number = 3265 Then '*** if the error is the query is missing
Resume Next '*** then skip the delete line and resume on
the next line
Else
MsgBox Err.Description '*** write out the error and exit the sub
Resume Exit_CreateQuery
End If

End Sub
____________________________________________________________________________
Function FillLabel(LabelNumber As Integer) As String
FillLabel = Nz(reportlabel(LabelNumber), "")

End Function

My crosstab query SQL is:

PARAMETERS [forms]![frm_NameData].[cbo_WEStartDate].[value] DateTime, [forms]!
[frm_NameData].[cbo_WEEndDate].[value] DateTime;
TRANSFORM Sum([All Data Query].HOURS) AS SumOfHOURS
SELECT [All Data Query].Project, [All Data Query].Function, [Last Name]+", "+
[First Name] AS TheName, Sum([All Data Query].HOURS) AS [Total Of HOURS]
FROM [All Data Query]
WHERE ((([All Data Query].[Week Ending Date]) Between ([forms]![frm_NameData].
[cbo_WEStartDate].[value]) And ([forms]![frm_NameData].[cbo_WEEndDate].[value]
)))
GROUP BY [All Data Query].Project, [All Data Query].Function, [Last Name]+",
"+[First Name]
ORDER BY Format([Week Ending Date],"mm/dd/yy")
PIVOT Format([Week Ending Date],"mm/dd/yy");

 

[Duane Hookom]

You don't need any code to do what you want. Assuming you have a date field
named [WorkDate] and a single text box on a form that you would enter the
end date [Forms]![frmDate]![txtEndDate]

Create your crosstab with a column heading expression of:
ColHead:"Wk" & DateDiff("ww",[WorkDate],[Forms]![frmDate]![txtEndDate])
Then, set the query parameter data type (Query->Parameters)
[Forms]![frmDate]![txtEndDate] Date/Time
Enter values into the Column Headings property of the crosstab
Column Headings: "Wk0","Wk1","Wk2","Wk3","Wk4"

--
Duane Hookom
MS Access MVP
--

I have been tasked to create a db that has employees, charge numbers, and
hours worked per week. i am trying to generate a report that displays up
to
5 weeks of summary data based on the work ending date.

I found a thread posted about a year ago called "dynamic crosstab A2002".
I
have used this thread to create my report but i get a blank report. If I
ener in the absolute date in the Criteria of the crosstab query the report
does work as expected. But if i enter in
Between ([forms]![frm_NameData].[cbo_WEStartDate].[value]) And ([forms]!
[frm_NameData].[cbo_WEEndDate].[value])
I get the blank report.

i looked at the created query based off of the crosstab query and the SQL
is:

SELECT Null AS Field0, Null AS Field1, Null AS Field2, Null AS Field3,
Null
AS Field4, Null AS Field5, Null AS Field6, Null AS Field7, Null AS Field8,
Null AS Field9
FROM Crosstab_Name2;

Why am i getting Null values?


My code for the report is:

Option Compare Database
Option Explicit
Dim reportlabel(10) As String
_______________________________________________
Private Sub Report_Open(Cancel As Integer)

'DoCmd.Maximize
Dim i As Integer
For i = 0 To 9
reportlabel(i) = ""
Next i
Call CreateReportQuery
End Sub
__________________________________________

Sub CreateReportQuery()
On Error GoTo Err_CreateQuery

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim indexx As Integer
Dim FieldList As String
Dim strSQL As String
Dim i As Integer
Dim j As Field

Set db = CurrentDb
Set qdf = db.QueryDefs("Crosstab_Name2")
indexx = 0

For Each fld In qdf.Fields
If fld.Type >= 1 And fld.Type <= 8 Or fld.Type = 10 Then
FieldList = FieldList & "[" & fld.name & "] as Field" & indexx
&
", "
reportlabel(indexx) = fld.name
End If

indexx = indexx + 1
Next fld
For i = indexx To 9
FieldList = FieldList & "null as Field" & i & ","
Next i
FieldList = Left(FieldList, Len(FieldList) - 1)

strSQL = "Select " & FieldList & " From Crosstab_Name2" & ";"
'MsgBox strSQL

db.QueryDefs.Delete "Crosstab_Report_Name2"
Set qdf = db.CreateQueryDef("Crosstab_Report_Name2", strSQL)

Exit_CreateQuery:
Exit Sub

Err_CreateQuery:
If Err.Number = 3265 Then '*** if the error is the query is missing
Resume Next '*** then skip the delete line and resume
on
the next line
Else
MsgBox Err.Description '*** write out the error and exit the
sub
Resume Exit_CreateQuery
End If

End Sub
____________________________________________________________________________
Function FillLabel(LabelNumber As Integer) As String
FillLabel = Nz(reportlabel(LabelNumber), "")

End Function

My crosstab query SQL is:

PARAMETERS [forms]![frm_NameData].[cbo_WEStartDate].[value] DateTime,
[forms]!
[frm_NameData].[cbo_WEEndDate].[value] DateTime;
TRANSFORM Sum([All Data Query].HOURS) AS SumOfHOURS
SELECT [All Data Query].Project, [All Data Query].Function, [Last Name]+",
"+
[First Name] AS TheName, Sum([All Data Query].HOURS) AS [Total Of HOURS]
FROM [All Data Query]
WHERE ((([All Data Query].[Week Ending Date]) Between
([forms]![frm_NameData].
[cbo_WEStartDate].[value]) And
([forms]![frm_NameData].[cbo_WEEndDate].[value]
)))
GROUP BY [All Data Query].Project, [All Data Query].Function, [Last
Name]+",
"+[First Name]
ORDER BY Format([Week Ending Date],"mm/dd/yy")
PIVOT Format([Week Ending Date],"mm/dd/yy");

 

[mtorr2x via AccessMonster.com]

Thank you for replying. Here is some more detail.

I have a form that has a combobox that limits the choices of the start date,
what i think you are calling [WorkDate]. The end date is calculated off the
start date. The report needs to display either one weeks summary take a
months work of data and show totals for each week.

An example is if a person selects 10/7/05 as the start date then the crosstab
report would show the following:

PROJECT 1
10/7/05 10/14/05
10/21/05 10/28/05 Total
Name1 0.3 1
5 6.3
Name2 6
8 0.1 14.1
Name3 8
8

PROJECT2
Name1 10
5 15
Name4 6
8 14
Name5 5
5

I tried what you suggested and it creates one column with the totals
depending on the week between start and end date. I need each week to be
split up.

I have taken this problem piece by piece and have things working if i select
absolute dates in the critera of the crosstab query. When i change the
criteria from absolute to a variable i get no data out of the report. I have
declared the variable in the parameter settings.

Any other suggestions? Any help would be greately appreciated.

You don't need any code to do what you want. Assuming you have a date field
named [WorkDate] and a single text box on a form that you would enter the
end date [Forms]![frmDate]![txtEndDate]

Create your crosstab with a column heading expression of:
ColHead:"Wk" & DateDiff("ww",[WorkDate],[Forms]![frmDate]![txtEndDate])
Then, set the query parameter data type (Query->Parameters)
[Forms]![frmDate]![txtEndDate] Date/Time
Enter values into the Column Headings property of the crosstab
Column Headings: "Wk0","Wk1","Wk2","Wk3","Wk4"

I have been tasked to create a db that has employees, charge numbers, and
hours worked per week. i am trying to generate a report that displays up

[quoted text clipped - 116 lines]

ORDER BY Format([Week Ending Date],"mm/dd/yy")
PIVOT Format([Week Ending Date],"mm/dd/yy");

 

[Duane Hookom]

Can you provide your SQL view?
What is the name of the form and control on the form that you use for the
criteria?
What is the name of your date field?

--
Duane Hookom
MS Access MVP
--

Thank you for replying. Here is some more detail.

I have a form that has a combobox that limits the choices of the start
date,
what i think you are calling [WorkDate]. The end date is calculated off
the
start date. The report needs to display either one weeks summary take a
months work of data and show totals for each week.

An example is if a person selects 10/7/05 as the start date then the
crosstab
report would show the following:

PROJECT 1
10/7/05
10/14/05
10/21/05 10/28/05 Total
Name1 0.3 1
5 6.3
Name2 6
8 0.1 14.1
Name3 8
8

PROJECT2
Name1 10
5 15
Name4 6
8 14
Name5
5
5

I tried what you suggested and it creates one column with the totals
depending on the week between start and end date. I need each week to be
split up.

I have taken this problem piece by piece and have things working if i
select
absolute dates in the critera of the crosstab query. When i change the
criteria from absolute to a variable i get no data out of the report. I
have
declared the variable in the parameter settings.

Any other suggestions? Any help would be greately appreciated.

You don't need any code to do what you want. Assuming you have a date
field
named [WorkDate] and a single text box on a form that you would enter the
end date [Forms]![frmDate]![txtEndDate]

Create your crosstab with a column heading expression of:
ColHead:"Wk" & DateDiff("ww",[WorkDate],[Forms]![frmDate]![txtEndDate])
Then, set the query parameter data type (Query->Parameters)
[Forms]![frmDate]![txtEndDate] Date/Time
Enter values into the Column Headings property of the crosstab
Column Headings: "Wk0","Wk1","Wk2","Wk3","Wk4"

I have been tasked to create a db that has employees, charge numbers, and
hours worked per week. i am trying to generate a report that displays
up

[quoted text clipped - 116 lines]

ORDER BY Format([Week Ending Date],"mm/dd/yy")
PIVOT Format([Week Ending Date],"mm/dd/yy");

 

[mtorr2x via AccessMonster.com]

I got it to work. I am not sure if it is the best or easy way to do it.
Here is what I did.

I created a form named "Menu_Reports". On this form is a button, Combobox
(cbo_dDate), 2 text boxes (Month, Year).

The cbo_dDate has 3 columsn ([Week Ending Date], [dMonth], [dYear]). The
[Month] and [Year] are defined by the user in a table becasue the year is not
fiscal and the [Week Ending Date] may or may not be in the month the actual
date is.

The code on combobox [cbo_dDate} is:
:
Private Sub cbo_dDate_AfterUpdate()
Me.dMonth = Me.cbo_dDate.Column(1)
Me.dYear = Me.cbo_dDate.Column(2)
End Sub

The code on the button is:

Private Sub cmd_mlyNamerpt_Click()
On Error GoTo Err_cmd_wklyProjectrpt_Click

Dim stQueryName As String
Dim stDocName As String

If IsNull(Me.cbo_dDate) Then
MsgBox "Please select the Week Ending Date you would like to start with."
Me.cbo_dDate.SetFocus
Exit Sub
End If

stQueryName = "Monthly_Name_Table"
DoCmd.SetWarnings False
DoCmd.OpenQuery stQueryName
DoCmd.SetWarnings True

stDocName = "rpt_Monthly_Name_Table"
DoCmd.OpenReport stDocName, acPreview

Exit_cmd_wklyProjectrpt_Click:
Exit Sub

Err_cmd_wklyProjectrpt_Click:
MsgBox Err.Description
Resume Exit_cmd_wklyProjectrpt_Click
End Sub

The [Monthly_Name_Table] query SQL is:

PARAMETERS [Forms]![Menu_Reports].[dMonth] Short, [Forms]![Menu_Reports].
[dYear] Short;
SELECT [All Data Query].Project, [All Data Query].Function, [Last Name]+", "+
[First Name] AS TheName, [All Data Query].[Week Ending Date], [All Data Query]
..HOURS INTO [Monthly Name Table]
FROM [All Data Query]
WHERE ((([All Data Query].Month)=[Forms]![Menu_Reports].[dMonth]) AND (([All
Data Query].Year)=[Forms]![Menu_Reports].[dYear]))
GROUP BY [All Data Query].Project, [All Data Query].Function, [Last Name]+",
"+[First Name], [All Data Query].[Week Ending Date], [All Data Query].HOURS
HAVING ((([All Data Query].[Week Ending Date])>=[Forms]![Menu_Reports].
[cbo_dDate]))
ORDER BY [All Data Query].[Week Ending Date];


Of couse the [Monthly_Name_Table] query creates a table of all the data i
need with the dates, month and years filtered out.

Now i open my report [rpt_Monthly_Name_Table]. The report contains unbound
text boxes and sources a query generated by code. The data text boxes have
sources Field 0-9. The label text boxes have a function called filllabel().


All the code for [rpt_Monthly_Name_Table] is:

Option Compare Database
Option Explicit
Dim reportlabel(10) As String
____________________________________________________________________
Private Sub Report_Open(Cancel As Integer)
'DoCmd.Maximize
Dim i As Integer
For i = 0 To 9
reportlabel(i) = ""
Next i
Call CreateReportQuery
End Sub
_____________________________________________________________________
Sub CreateReportQuery()
On Error GoTo Err_CreateQuery

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim indexx As Integer
Dim FieldList As String
Dim strSQL As String
Dim i As Integer
Dim j As Field

Set db = CurrentDb
Set qdf = db.QueryDefs("Monthly_Name_Table_Crosstab")
indexx = 0

For Each fld In qdf.Fields
If fld.Type >= 1 And fld.Type <= 8 Or fld.Type = 10 Then
FieldList = FieldList & "[" & fld.Name & "] as Field" & indexx &
", "
reportlabel(indexx) = fld.Name
End If
'MsgBox Label(indexx)
indexx = indexx + 1
Next fld
For i = indexx To 9
FieldList = FieldList & "null as Field" & i & ","
Next i
FieldList = Left(FieldList, Len(FieldList) - 1)

strSQL = "Select " & FieldList & " From [Monthly_Name_Table_Crosstab]" & ";"
'MsgBox strSQL

db.QueryDefs.Delete "Monthly_Name_Table_Crosstab_Report"
Set qdf = db.CreateQueryDef("Monthly_Name_Table_Crosstab_Report", strSQL)

Exit_CreateQuery:
Exit Sub

Err_CreateQuery:
If Err.Number = 3265 Then '*** if the error is the query is missing
Resume Next '*** then skip the delete line and resume on
the next line
Else
MsgBox Err.Description '*** write out the error and exit the sub
Resume Exit_CreateQuery
End If

End Sub
________________________________________________________
Function FillLabel(LabelNumber As Integer) As String
FillLabel = Nz(reportlabel(LabelNumber), "")
End Function
________________________________________________________


The crosstab report ([Monthly_Name_Table_Crosstab}) based of the table
generated by the [Monthly_Name_table] SQL is:

TRANSFORM Sum([Monthly Project Table].HOURS) AS SumOfHOURS
SELECT [Monthly Project Table].Project, [Monthly Project Table].Function, Sum
([Monthly Project Table].HOURS) AS [Total Of HOURS]
FROM [Monthly Project Table]
GROUP BY [Monthly Project Table].Project, [Monthly Project Table].Function
ORDER BY [Monthly Project Table].[Week Ending Date]
PIVOT [Monthly Project Table].[Week Ending Date];

Thanks for your help.

Can you provide your SQL view?
What is the name of the form and control on the form that you use for the
criteria?
What is the name of your date field?

Thank you for replying. Here is some more detail.

[quoted text clipped - 60 lines]

ORDER BY Format([Week Ending Date],"mm/dd/yy")
PIVOT Format([Week Ending Date],"mm/dd/yy");

 

[Duane Hookom]

If you got it to work, that's great. I'm not about to decipher the code if
it works for you. I still don't think you need any code to create a crosstab
report with date intervals as column headings.

--
Duane Hookom
MS Access MVP
--

I got it to work. I am not sure if it is the best or easy way to do it.
Here is what I did.

I created a form named "Menu_Reports". On this form is a button, Combobox
(cbo_dDate), 2 text boxes (Month, Year).

The cbo_dDate has 3 columsn ([Week Ending Date], [dMonth], [dYear]). The
[Month] and [Year] are defined by the user in a table becasue the year is
not
fiscal and the [Week Ending Date] may or may not be in the month the
actual
date is.

The code on combobox [cbo_dDate} is:
:
Private Sub cbo_dDate_AfterUpdate()
Me.dMonth = Me.cbo_dDate.Column(1)
Me.dYear = Me.cbo_dDate.Column(2)
End Sub

The code on the button is:

Private Sub cmd_mlyNamerpt_Click()
On Error GoTo Err_cmd_wklyProjectrpt_Click

Dim stQueryName As String
Dim stDocName As String

If IsNull(Me.cbo_dDate) Then
MsgBox "Please select the Week Ending Date you would like to start
with."
Me.cbo_dDate.SetFocus
Exit Sub
End If

stQueryName = "Monthly_Name_Table"
DoCmd.SetWarnings False
DoCmd.OpenQuery stQueryName
DoCmd.SetWarnings True

stDocName = "rpt_Monthly_Name_Table"
DoCmd.OpenReport stDocName, acPreview

Exit_cmd_wklyProjectrpt_Click:
Exit Sub

Err_cmd_wklyProjectrpt_Click:
MsgBox Err.Description
Resume Exit_cmd_wklyProjectrpt_Click
End Sub

The [Monthly_Name_Table] query SQL is:

PARAMETERS [Forms]![Menu_Reports].[dMonth] Short, [Forms]![Menu_Reports].
[dYear] Short;
SELECT [All Data Query].Project, [All Data Query].Function, [Last Name]+",
"+
[First Name] AS TheName, [All Data Query].[Week Ending Date], [All Data
Query]
.HOURS INTO [Monthly Name Table]
FROM [All Data Query]
WHERE ((([All Data Query].Month)=[Forms]![Menu_Reports].[dMonth]) AND
(([All
Data Query].Year)=[Forms]![Menu_Reports].[dYear]))
GROUP BY [All Data Query].Project, [All Data Query].Function, [Last
Name]+",
"+[First Name], [All Data Query].[Week Ending Date], [All Data
Query].HOURS
HAVING ((([All Data Query].[Week Ending Date])>=[Forms]![Menu_Reports].
[cbo_dDate]))
ORDER BY [All Data Query].[Week Ending Date];


Of couse the [Monthly_Name_Table] query creates a table of all the data i
need with the dates, month and years filtered out.

Now i open my report [rpt_Monthly_Name_Table]. The report contains
unbound
text boxes and sources a query generated by code. The data text boxes
have
sources Field 0-9. The label text boxes have a function called
filllabel().


All the code for [rpt_Monthly_Name_Table] is:

Option Compare Database
Option Explicit
Dim reportlabel(10) As String
____________________________________________________________________
Private Sub Report_Open(Cancel As Integer)
'DoCmd.Maximize
Dim i As Integer
For i = 0 To 9
reportlabel(i) = ""
Next i
Call CreateReportQuery
End Sub
_____________________________________________________________________
Sub CreateReportQuery()
On Error GoTo Err_CreateQuery

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim indexx As Integer
Dim FieldList As String
Dim strSQL As String
Dim i As Integer
Dim j As Field

Set db = CurrentDb
Set qdf = db.QueryDefs("Monthly_Name_Table_Crosstab")
indexx = 0

For Each fld In qdf.Fields
If fld.Type >= 1 And fld.Type <= 8 Or fld.Type = 10 Then
FieldList = FieldList & "[" & fld.Name & "] as Field" & indexx
&
", "
reportlabel(indexx) = fld.Name
End If
'MsgBox Label(indexx)
indexx = indexx + 1
Next fld
For i = indexx To 9
FieldList = FieldList & "null as Field" & i & ","
Next i
FieldList = Left(FieldList, Len(FieldList) - 1)

strSQL = "Select " & FieldList & " From [Monthly_Name_Table_Crosstab]" &
";"
'MsgBox strSQL

db.QueryDefs.Delete "Monthly_Name_Table_Crosstab_Report"
Set qdf = db.CreateQueryDef("Monthly_Name_Table_Crosstab_Report",
strSQL)

Exit_CreateQuery:
Exit Sub

Err_CreateQuery:
If Err.Number = 3265 Then '*** if the error is the query is missing
Resume Next '*** then skip the delete line and resume
on
the next line
Else
MsgBox Err.Description '*** write out the error and exit the
sub
Resume Exit_CreateQuery
End If

End Sub
________________________________________________________
Function FillLabel(LabelNumber As Integer) As String
FillLabel = Nz(reportlabel(LabelNumber), "")
End Function
________________________________________________________


The crosstab report ([Monthly_Name_Table_Crosstab}) based of the table
generated by the [Monthly_Name_table] SQL is:

TRANSFORM Sum([Monthly Project Table].HOURS) AS SumOfHOURS
SELECT [Monthly Project Table].Project, [Monthly Project Table].Function,
Sum
([Monthly Project Table].HOURS) AS [Total Of HOURS]
FROM [Monthly Project Table]
GROUP BY [Monthly Project Table].Project, [Monthly Project Table].Function
ORDER BY [Monthly Project Table].[Week Ending Date]
PIVOT [Monthly Project Table].[Week Ending Date];

Thanks for your help.

Can you provide your SQL view?
What is the name of the form and control on the form that you use for the
criteria?
What is the name of your date field?

Thank you for replying. Here is some more detail.

[quoted text clipped - 60 lines]

ORDER BY Format([Week Ending Date],"mm/dd/yy")
PIVOT Format([Week Ending Date],"mm/dd/yy");