dynamic crosstab A2002

[Brigitte P]

I'm on my end. I have a crosstab query with 2 row headings (a category and
subcategory) and then an EventDate which results in the column going Jan,
Feb, Mar ..... Dec. The query works fine, except it gives me all these empty
columns when I select less then 12 months. I know, it's an old problem.
I tried the code that Microsoft has published, downloaded the solution
database and copied the form and code from there, just changing to my form
and query names, but it doesn't work. I suppose this is because I have 2 row
headings instead one and deal with 12 date colums vs. the 9 they had
provided for. I added the extra columns, headers, totals to come to the
total 15 required, but that didn't help.
I then went to the site recommended by Duane Hookom with the "Mth0"
solution. This works as long as the underlying data is static, that is, as
long as I select the same number of month that I have allowed for in the
report. If I have Mth1, Mth 2, Mth 3 in the report and select the next time
6 month, 3 of them won't show up. I tried the Pivot Table solution, but that
is not what I need.
I'm about to pull out my hair; don't know how to do it, and don't know
enough about the Microsoft VBA to fix it.
The only solution I could find, have the end user put in the values for the
static colums (e.g., if they select from Aug - Oct they would have to type
Aug, Sep, Oct in the Column Headings query property. Is there a way to
prompt the user for this or do I need to make the crosstab query available
to her and hope that she doesn't forget or "mess up"?
Or, maybe a kind sould could copy the Microsoft solution, correct for my
extra head, col, tot rows, and email it to me. My end result needs to be:

Event Subevent Total Aug Sep Oct (or Jan, Feb,
Mar) (or whatever combination of month the user needs)
Fall Slippery 12 2 4 6
Medcial Flu 3 1 2

Many thanks in advance for any help.
Brigitte P

 

[Duane Hookom]

You could use the Mth0 solution combined with some code in the On Open event
of the report that would hide some columns that the user doesn't want to
see.

Another method is to use the Query By Form applet at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. You
would create a generic crosstab with the maximum number of months as save
the query with a name like "<Events By Month>". This would expose the query
so your users could select any fields and set any criteria to view the data.
The records are a couple clicks from sending to printer, html, csv, Excel,
Word table, Word Merge, graph,...

 

[Brigitte P]

I used the solution CrosstabReport2k from the website you recommended. The
report is based on a query of acrosstab query, renames the fields from the
crosstab, and code behind the report hides/shows colums/labels as needed. I
could figure this out. My little pesky problem is that I can't set the
column headings in the crosstab, and therefore, my crosstab won't accept my
data parameters in the Where clause of the Date field. It will accept the
absolute date values, e.g. Between #5/1/04# and #10/30/04# but not Between
Forms![formname]![StartDate] And Forms![formname]![EndDate]
I tried to set the dates in the query underlying the crosstab, tried it in
the Parameter fields of the crosstab and in the Where clause. The date field
no longer exists in the query underlying the report, because the date has
become the column value.
I know that it has to do with column headings not being set in the crosstab,
but when I set them, the report doesn't work any longer.
Is there a way around this.
Again, many thanks for your help.
Brigitte P.

 

[Duane Hookom]

Crosstab queries require the explicit identification of the parameter data
types. While in the design view, select Query->Parameters and enter
Forms![formname]![StartDate] Date/Time
Forms![formname]![EndDate] Date/Time

--
Duane Hookom
MS Access MVP

I used the solution CrosstabReport2k from the website you recommended. The
report is based on a query of acrosstab query, renames the fields from
the
crosstab, and code behind the report hides/shows colums/labels as needed.
I
could figure this out. My little pesky problem is that I can't set the
column headings in the crosstab, and therefore, my crosstab won't accept
my
data parameters in the Where clause of the Date field. It will accept the
absolute date values, e.g. Between #5/1/04# and #10/30/04# but not Between
Forms![formname]![StartDate] And Forms![formname]![EndDate]
I tried to set the dates in the query underlying the crosstab, tried it in
the Parameter fields of the crosstab and in the Where clause. The date
field
no longer exists in the query underlying the report, because the date has
become the column value.
I know that it has to do with column headings not being set in the
crosstab,
but when I set them, the report doesn't work any longer.
Is there a way around this.
Again, many thanks for your help.
Brigitte P.

You could use the Mth0 solution combined with some code in the On Open event
of the report that would hide some columns that the user doesn't want to
see.

Another method is to use the Query By Form applet at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. You
would create a generic crosstab with the maximum number of months as save
the query with a name like "<Events By Month>". This would expose the query
so your users could select any fields and set any criteria to view the data.
The records are a couple clicks from sending to printer, html, csv,
Excel,
Word table, Word Merge, graph,...

 

[Brigitte P]

Duane,
The parameter setting worked just fine, even though I had to go to SQL view
to get the brackets right. However, I have to ask for one more favor. In the
report that works for me, the code obviously has not set the parameter
definition. I tried to get it together by taking snippets of code from here
and there, but I can't get it. If you don't mind helping me through the last
step then I can apply this to many other crosstab reports to come. Following
is the code that makes the report work:

My parameters are: Forms![PGH SPECIFIC REPORTS]!txtStartDate and Forms![PGH
SPECIFIC REPORTS]!txtEndDate


Option Compare Database
Option Explicit
Dim ReportLabel(16) As String

Private Sub Report_Open(Cancel As Integer)
DoCmd.Maximize
Dim i As Integer
For i = 0 To 16
ReportLabel(i) = ""
Next i
Call CreateReportQuery
End Sub

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
Dim i As Integer
For i = 1 To 16
If FillLabel(i) <> "" Then
Me("line" & i & "1").Visible = True
Me("line" & i & "2").Visible = True
Me("line" & i & "3").Visible = True
Else
Me("line" & i & "1").Visible = False
Me("line" & i & "2").Visible = False
Me("line" & i & "3").Visible = False
End If
Next i
End Sub

Sub CreateReportQuery()
On Error GoTo Err_CreateQuery
Dim db As DAO.Database
Dim rs As DAO.Recordset
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

Set db = CurrentDb
Set qdf = db.QueryDefs("qryMonthlyIncidentsBase_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 15
FieldList = FieldList & "null as Field" & i & ","
Next i
FieldList = Left(FieldList, Len(FieldList) - 1)

strSQL = "Select " & FieldList & " From qryMonthlyIncidentsBase_Crosstab"

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

'MsgBox 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 qryCrossTabReport is based on the underlying crosstab query
qryMonthlyIncidentsBase_Crosstab.

I tried to find the person who originated the above solution to credit in my
dbase, but I can't even find that anymore. I just downloaded and worked from
there.

Again, I appreciate your help (you helped me a few years back through
another problem that worked real well for me). Happy New Year.
Brigitte

Crosstab queries require the explicit identification of the parameter data
types. While in the design view, select Query->Parameters and enter
Forms![formname]![StartDate] Date/Time
Forms![formname]![EndDate] Date/Time

--
Duane Hookom
MS Access MVP

I used the solution CrosstabReport2k from the website you recommended. The
report is based on a query of acrosstab query, renames the fields from
the
crosstab, and code behind the report hides/shows colums/labels as needed.
I
could figure this out. My little pesky problem is that I can't set the
column headings in the crosstab, and therefore, my crosstab won't accept
my
data parameters in the Where clause of the Date field. It will accept the
absolute date values, e.g. Between #5/1/04# and #10/30/04# but not Between
Forms![formname]![StartDate] And Forms![formname]![EndDate]
I tried to set the dates in the query underlying the crosstab, tried it in
the Parameter fields of the crosstab and in the Where clause. The date
field
no longer exists in the query underlying the report, because the date has
become the column value.
I know that it has to do with column headings not being set in the
crosstab,
but when I set them, the report doesn't work any longer.
Is there a way around this.
Again, many thanks for your help.
Brigitte P.

You could use the Mth0 solution combined with some code in the On Open event
of the report that would hide some columns that the user doesn't want to
see.

Another method is to use the Query By Form applet at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. You
would create a generic crosstab with the maximum number of months as save
the query with a name like "<Events By Month>". This would expose the query
so your users could select any fields and set any criteria to view the data.
The records are a couple clicks from sending to printer, html, csv,
Excel,
Word table, Word Merge, graph,...

category
and

subcategory) and then an EventDate which results in the column going Jan,
Feb, Mar ..... Dec. The query works fine, except it gives me all these
empty
columns when I select less then 12 months. I know, it's an old problem.
I tried the code that Microsoft has published, downloaded the solution
database and copied the form and code from there, just changing to my form
and query names, but it doesn't work. I suppose this is because I have
2
row
headings instead one and deal with 12 date colums vs. the 9 they had
provided for. I added the extra columns, headers, totals to come to the
total 15 required, but that didn't help.
I then went to the site recommended by Duane Hookom with the "Mth0"
solution. This works as long as the underlying data is static, that

is,
as

long as I select the same number of month that I have allowed for in
the
report. If I have Mth1, Mth 2, Mth 3 in the report and select the next
time
6 month, 3 of them won't show up. I tried the Pivot Table solution, but
that
is not what I need.
I'm about to pull out my hair; don't know how to do it, and don't know
enough about the Microsoft VBA to fix it.
The only solution I could find, have the end user put in the values for
the
static colums (e.g., if they select from Aug - Oct they would have to type
Aug, Sep, Oct in the Column Headings query property. Is there a way to
prompt the user for this or do I need to make the crosstab query available
to her and hope that she doesn't forget or "mess up"?
Or, maybe a kind sould could copy the Microsoft solution, correct for
my
extra head, col, tot rows, and email it to me. My end result needs to
be:

Event Subevent Total Aug Sep Oct (or Jan,
Feb,
Mar) (or whatever combination of month the user needs)
Fall Slippery 12 2 4 6
Medcial Flu 3 1 2

Many thanks in advance for any help.
Brigitte P

 

[Duane Hookom]

If you are using a solution from Rogers Access or Corporate Technologies
(invisibleinc.com) then I probably created it. I don't know which solution
you are using. I don't think you need to open any recordset/querydef in code
since your columns of the crosstab can be determined based on the start and
end dates. It would help if you shared your SQL view.

--
Duane Hookom
MS Access MVP

Duane,
The parameter setting worked just fine, even though I had to go to SQL
view
to get the brackets right. However, I have to ask for one more favor. In
the
report that works for me, the code obviously has not set the parameter
definition. I tried to get it together by taking snippets of code from
here
and there, but I can't get it. If you don't mind helping me through the
last
step then I can apply this to many other crosstab reports to come.
Following
is the code that makes the report work:

My parameters are: Forms![PGH SPECIFIC REPORTS]!txtStartDate and
Forms![PGH
SPECIFIC REPORTS]!txtEndDate


Option Compare Database
Option Explicit
Dim ReportLabel(16) As String

Private Sub Report_Open(Cancel As Integer)
DoCmd.Maximize
Dim i As Integer
For i = 0 To 16
ReportLabel(i) = ""
Next i
Call CreateReportQuery
End Sub

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
Dim i As Integer
For i = 1 To 16
If FillLabel(i) <> "" Then
Me("line" & i & "1").Visible = True
Me("line" & i & "2").Visible = True
Me("line" & i & "3").Visible = True
Else
Me("line" & i & "1").Visible = False
Me("line" & i & "2").Visible = False
Me("line" & i & "3").Visible = False
End If
Next i
End Sub

Sub CreateReportQuery()
On Error GoTo Err_CreateQuery
Dim db As DAO.Database
Dim rs As DAO.Recordset
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

Set db = CurrentDb
Set qdf = db.QueryDefs("qryMonthlyIncidentsBase_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 15
FieldList = FieldList & "null as Field" & i & ","
Next i
FieldList = Left(FieldList, Len(FieldList) - 1)

strSQL = "Select " & FieldList & " From qryMonthlyIncidentsBase_Crosstab"

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

'MsgBox 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 qryCrossTabReport is based on the underlying crosstab query
qryMonthlyIncidentsBase_Crosstab.

I tried to find the person who originated the above solution to credit in
my
dbase, but I can't even find that anymore. I just downloaded and worked
from
there.

Again, I appreciate your help (you helped me a few years back through
another problem that worked real well for me). Happy New Year.
Brigitte

Crosstab queries require the explicit identification of the parameter
data
types. While in the design view, select Query->Parameters and enter
Forms![formname]![StartDate] Date/Time
Forms![formname]![EndDate] Date/Time

--
Duane Hookom
MS Access MVP

I used the solution CrosstabReport2k from the website you recommended. The
report is based on a query of acrosstab query, renames the fields from
the
crosstab, and code behind the report hides/shows colums/labels as needed.
I
could figure this out. My little pesky problem is that I can't set the
column headings in the crosstab, and therefore, my crosstab won't
accept
my
data parameters in the Where clause of the Date field. It will accept the
absolute date values, e.g. Between #5/1/04# and #10/30/04# but not Between
Forms![formname]![StartDate] And Forms![formname]![EndDate]
I tried to set the dates in the query underlying the crosstab, tried it in
the Parameter fields of the crosstab and in the Where clause. The date
field
no longer exists in the query underlying the report, because the date has
become the column value.
I know that it has to do with column headings not being set in the
crosstab,
but when I set them, the report doesn't work any longer.
Is there a way around this.
Again, many thanks for your help.
Brigitte P.

You could use the Mth0 solution combined with some code in the On Open
event
of the report that would hide some columns that the user doesn't want to
see.

Another method is to use the Query By Form applet at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.
You
would create a generic crosstab with the maximum number of months as save
the query with a name like "<Events By Month>". This would expose the
query
so your users could select any fields and set any criteria to view the
data.
The records are a couple clicks from sending to printer, html, csv,
Excel,
Word table, Word Merge, graph,...

--
Duane Hookom
MS Access MVP
--

I'm on my end. I have a crosstab query with 2 row headings (a category
and
subcategory) and then an EventDate which results in the column going
Jan,
Feb, Mar ..... Dec. The query works fine, except it gives me all these
empty
columns when I select less then 12 months. I know, it's an old problem.
I tried the code that Microsoft has published, downloaded the solution
database and copied the form and code from there, just changing to
my
form
and query names, but it doesn't work. I suppose this is because I have
2
row
headings instead one and deal with 12 date colums vs. the 9 they had
provided for. I added the extra columns, headers, totals to come to the
total 15 required, but that didn't help.
I then went to the site recommended by Duane Hookom with the "Mth0"
solution. This works as long as the underlying data is static, that is,
as
long as I select the same number of month that I have allowed for in
the
report. If I have Mth1, Mth 2, Mth 3 in the report and select the next
time
6 month, 3 of them won't show up. I tried the Pivot Table solution, but
that
is not what I need.
I'm about to pull out my hair; don't know how to do it, and don't know
enough about the Microsoft VBA to fix it.
The only solution I could find, have the end user put in the values for
the
static colums (e.g., if they select from Aug - Oct they would have
to
type
Aug, Sep, Oct in the Column Headings query property. Is there a way to
prompt the user for this or do I need to make the crosstab query
available
to her and hope that she doesn't forget or "mess up"?
Or, maybe a kind sould could copy the Microsoft solution, correct
for
my
extra head, col, tot rows, and email it to me. My end result needs to
be:

Event Subevent Total Aug Sep Oct (or Jan,
Feb,
Mar) (or whatever combination of month the user needs)
Fall Slippery 12 2 4 6
Medcial Flu 3 1 2

Many thanks in advance for any help.
Brigitte P

 

[Brigitte P]

The solution is from Rogers Access and the report runs fine when I set the
query with absolute values. However, the report comes up blank when I use
the form parameters. Following is the SQL from the Crosstab and second from
the query that is based on the crosstab:

Query 1: QryMonthlyIncidentsBase_Crosstab

PARAMETERS [Forms]![PGH SPECIFIC REPORTS]![txtStartDate] DateTime,
[Forms]![PGH SPECIFIC REPORTS]![txtEndDate] DateTime;
TRANSFORM Count(qryMonthlyIncidentsBase.EventEvent) AS CountOfEventEvent
SELECT qryMonthlyIncidentsBase.EventEvent,
qryMonthlyIncidentsBase.EventSubcategory,
Count(qryMonthlyIncidentsBase.EventEvent) AS [Total Of EventEvent]
FROM qryMonthlyIncidentsBase
WHERE (((qryMonthlyIncidentsBase.EventDate) Between [Forms]![PGH SPECIFIC
REPORTS]![txtStartDate] And [Forms]![PGH SPECIFIC REPORTS]![txtEndDate]))
GROUP BY qryMonthlyIncidentsBase.EventEvent,
qryMonthlyIncidentsBase.EventSubcategory
ORDER BY qryMonthlyIncidentsBase.EventEvent,
qryMonthlyIncidentsBase.EventSubcategory
PIVOT Format([EventDate],"mm ");

Query2 - The one the report is bound to: qryCrosstabReport

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, null AS Field10, null AS Field11, null AS Field12, null AS
Field13, null AS Field14, null AS Field15
FROM qryMonthlyIncidentsBase_Crosstab;

Thanks for being so patient with me. Working with someone like you shows me
how little I know even though I have developed many databases over the
years, and they run fine once I worked my way through with the help of the
user groups. I do this in addition to my main job, and I know only the very
basics of programming. I used to know Basic for DOS (can you tell how
ancient I am and that helps. But I can do only very, very little VBA and
practically to SQL, even though I can understand both basically. Wouldn't
know what to do without the user groups. Your folks are great.
Brigitte

If you are using a solution from Rogers Access or Corporate Technologies
(invisibleinc.com) then I probably created it. I don't know which solution
you are using. I don't think you need to open any recordset/querydef in code
since your columns of the crosstab can be determined based on the start and
end dates. It would help if you shared your SQL view.

--
Duane Hookom
MS Access MVP

Duane,
The parameter setting worked just fine, even though I had to go to SQL
view
to get the brackets right. However, I have to ask for one more favor. In
the
report that works for me, the code obviously has not set the parameter
definition. I tried to get it together by taking snippets of code from
here
and there, but I can't get it. If you don't mind helping me through the
last
step then I can apply this to many other crosstab reports to come.
Following
is the code that makes the report work:

My parameters are: Forms![PGH SPECIFIC REPORTS]!txtStartDate and
Forms![PGH
SPECIFIC REPORTS]!txtEndDate


Option Compare Database
Option Explicit
Dim ReportLabel(16) As String

Private Sub Report_Open(Cancel As Integer)
DoCmd.Maximize
Dim i As Integer
For i = 0 To 16
ReportLabel(i) = ""
Next i
Call CreateReportQuery
End Sub

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
Dim i As Integer
For i = 1 To 16
If FillLabel(i) <> "" Then
Me("line" & i & "1").Visible = True
Me("line" & i & "2").Visible = True
Me("line" & i & "3").Visible = True
Else
Me("line" & i & "1").Visible = False
Me("line" & i & "2").Visible = False
Me("line" & i & "3").Visible = False
End If
Next i
End Sub

Sub CreateReportQuery()
On Error GoTo Err_CreateQuery
Dim db As DAO.Database
Dim rs As DAO.Recordset
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

Set db = CurrentDb
Set qdf = db.QueryDefs("qryMonthlyIncidentsBase_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 15
FieldList = FieldList & "null as Field" & i & ","
Next i
FieldList = Left(FieldList, Len(FieldList) - 1)

strSQL = "Select " & FieldList & " From qryMonthlyIncidentsBase_Crosstab"

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

'MsgBox 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 qryCrossTabReport is based on the underlying crosstab query
qryMonthlyIncidentsBase_Crosstab.

I tried to find the person who originated the above solution to credit in
my
dbase, but I can't even find that anymore. I just downloaded and worked
from
there.

Again, I appreciate your help (you helped me a few years back through
another problem that worked real well for me). Happy New Year.
Brigitte

Crosstab queries require the explicit identification of the parameter
data
types. While in the design view, select Query->Parameters and enter
Forms![formname]![StartDate] Date/Time
Forms![formname]![EndDate] Date/Time

--
Duane Hookom
MS Access MVP


I used the solution CrosstabReport2k from the website you recommended. The
report is based on a query of acrosstab query, renames the fields from
the
crosstab, and code behind the report hides/shows colums/labels as needed.
I
could figure this out. My little pesky problem is that I can't set the
column headings in the crosstab, and therefore, my crosstab won't
accept
my
data parameters in the Where clause of the Date field. It will accept the
absolute date values, e.g. Between #5/1/04# and #10/30/04# but not Between
Forms![formname]![StartDate] And Forms![formname]![EndDate]
I tried to set the dates in the query underlying the crosstab, tried

it
in

the Parameter fields of the crosstab and in the Where clause. The date
field
no longer exists in the query underlying the report, because the date has
become the column value.
I know that it has to do with column headings not being set in the
crosstab,
but when I set them, the report doesn't work any longer.
Is there a way around this.
Again, many thanks for your help.
Brigitte P.

You could use the Mth0 solution combined with some code in the On Open
event
of the report that would hide some columns that the user doesn't

want
to

see.

Another method is to use the Query By Form applet at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.
You
would create a generic crosstab with the maximum number of months as save
the query with a name like "<Events By Month>". This would expose the
query
so your users could select any fields and set any criteria to view the
data.
The records are a couple clicks from sending to printer, html, csv,
Excel,
Word table, Word Merge, graph,...

--
Duane Hookom
MS Access MVP
--

I'm on my end. I have a crosstab query with 2 row headings (a category
and
subcategory) and then an EventDate which results in the column going
Jan,
Feb, Mar ..... Dec. The query works fine, except it gives me all these
empty
columns when I select less then 12 months. I know, it's an old problem.
I tried the code that Microsoft has published, downloaded the solution
database and copied the form and code from there, just changing to
my
form
and query names, but it doesn't work. I suppose this is because I have
2
row
headings instead one and deal with 12 date colums vs. the 9 they had
provided for. I added the extra columns, headers, totals to come

to
the

total 15 required, but that didn't help.
I then went to the site recommended by Duane Hookom with the "Mth0"
solution. This works as long as the underlying data is static,

that
is,

as
long as I select the same number of month that I have allowed for in
the
report. If I have Mth1, Mth 2, Mth 3 in the report and select the next
time
6 month, 3 of them won't show up. I tried the Pivot Table

solution,
but

that
is not what I need.
I'm about to pull out my hair; don't know how to do it, and don't know
enough about the Microsoft VBA to fix it.
The only solution I could find, have the end user put in the

values
for

the
static colums (e.g., if they select from Aug - Oct they would have
to
type
Aug, Sep, Oct in the Column Headings query property. Is there a

way
to

prompt the user for this or do I need to make the crosstab query
available
to her and hope that she doesn't forget or "mess up"?
Or, maybe a kind sould could copy the Microsoft solution, correct
for
my
extra head, col, tot rows, and email it to me. My end result

needs
to

be:

Event Subevent Total Aug Sep Oct (or Jan,
Feb,
Mar) (or whatever combination of month the user needs)
Fall Slippery 12 2 4 6
Medcial Flu 3 1 2

Many thanks in advance for any help.
Brigitte P

 

[Duane Hookom]

I would set up the crosstab (report's record source) as
Query 1: QryMonthlyIncidentsBase_Crosstab

PARAMETERS [Forms]![PGH SPECIFIC REPORTS]![txtStartDate] DateTime,
[Forms]![PGH SPECIFIC REPORTS]![txtEndDate] DateTime;
TRANSFORM Count(qryMonthlyIncidentsBase.EventEvent) AS CountOfEventEvent
SELECT qryMonthlyIncidentsBase.EventEvent,
qryMonthlyIncidentsBase.EventSubcategory,
Count(qryMonthlyIncidentsBase.EventEvent) AS [Total Of EventEvent]
FROM qryMonthlyIncidentsBase
WHERE (((qryMonthlyIncidentsBase.EventDate) Between [Forms]![PGH SPECIFIC
REPORTS]![txtStartDate] And [Forms]![PGH SPECIFIC REPORTS]![txtEndDate]))
GROUP BY qryMonthlyIncidentsBase.EventEvent,
qryMonthlyIncidentsBase.EventSubcategory
ORDER BY qryMonthlyIncidentsBase.EventEvent,
qryMonthlyIncidentsBase.EventSubcategory
PIVOT "M" & DateDiff("m",[EventDate],
Forms![PGH SPECIFIC REPORTS]!txtStartDate) IN
("M0","M1","M2","M3",..."M11");

If txtEndDate is less than 12 months past txtStartDate, then the last month
columns will be blank/null. Other columns beginning with M0 should have data
values. Base your report on this query.

You can set column headings/labels in the report with text boxes with
control sources like:
=DateAdd("m",0,Forms![PGH SPECIFIC REPORTS]!txtStartDate)
=DateAdd("m",1,Forms![PGH SPECIFIC REPORTS]!txtStartDate)
=DateAdd("m",2,Forms![PGH SPECIFIC REPORTS]!txtStartDate)
=...
You can use code in the On Format event of the section containing the
headings to make some invisible based on the difference between the entered
start and end dates.
--
Duane Hookom
MS Access MVP

The solution is from Rogers Access and the report runs fine when I set the
query with absolute values. However, the report comes up blank when I use
the form parameters. Following is the SQL from the Crosstab and second
from
the query that is based on the crosstab:

Query 1: QryMonthlyIncidentsBase_Crosstab

PARAMETERS [Forms]![PGH SPECIFIC REPORTS]![txtStartDate] DateTime,
[Forms]![PGH SPECIFIC REPORTS]![txtEndDate] DateTime;
TRANSFORM Count(qryMonthlyIncidentsBase.EventEvent) AS CountOfEventEvent
SELECT qryMonthlyIncidentsBase.EventEvent,
qryMonthlyIncidentsBase.EventSubcategory,
Count(qryMonthlyIncidentsBase.EventEvent) AS [Total Of EventEvent]
FROM qryMonthlyIncidentsBase
WHERE (((qryMonthlyIncidentsBase.EventDate) Between [Forms]![PGH SPECIFIC
REPORTS]![txtStartDate] And [Forms]![PGH SPECIFIC REPORTS]![txtEndDate]))
GROUP BY qryMonthlyIncidentsBase.EventEvent,
qryMonthlyIncidentsBase.EventSubcategory
ORDER BY qryMonthlyIncidentsBase.EventEvent,
qryMonthlyIncidentsBase.EventSubcategory
PIVOT Format([EventDate],"mm ");

Query2 - The one the report is bound to: qryCrosstabReport

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, null AS Field10, null AS Field11, null AS Field12, null AS
Field13, null AS Field14, null AS Field15
FROM qryMonthlyIncidentsBase_Crosstab;

Thanks for being so patient with me. Working with someone like you shows
me
how little I know even though I have developed many databases over the
years, and they run fine once I worked my way through with the help of the
user groups. I do this in addition to my main job, and I know only the
very
basics of programming. I used to know Basic for DOS (can you tell how
ancient I am and that helps. But I can do only very, very little VBA and
practically to SQL, even though I can understand both basically. Wouldn't
know what to do without the user groups. Your folks are great.
Brigitte

If you are using a solution from Rogers Access or Corporate Technologies
(invisibleinc.com) then I probably created it. I don't know which
solution
you are using. I don't think you need to open any recordset/querydef in code
since your columns of the crosstab can be determined based on the start and
end dates. It would help if you shared your SQL view.

--
Duane Hookom
MS Access MVP

Duane,
The parameter setting worked just fine, even though I had to go to SQL
view
to get the brackets right. However, I have to ask for one more favor.
In
the
report that works for me, the code obviously has not set the parameter
definition. I tried to get it together by taking snippets of code from
here
and there, but I can't get it. If you don't mind helping me through the
last
step then I can apply this to many other crosstab reports to come.
Following
is the code that makes the report work:

My parameters are: Forms![PGH SPECIFIC REPORTS]!txtStartDate and
Forms![PGH
SPECIFIC REPORTS]!txtEndDate


Option Compare Database
Option Explicit
Dim ReportLabel(16) As String

Private Sub Report_Open(Cancel As Integer)
DoCmd.Maximize
Dim i As Integer
For i = 0 To 16
ReportLabel(i) = ""
Next i
Call CreateReportQuery
End Sub

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
Dim i As Integer
For i = 1 To 16
If FillLabel(i) <> "" Then
Me("line" & i & "1").Visible = True
Me("line" & i & "2").Visible = True
Me("line" & i & "3").Visible = True
Else
Me("line" & i & "1").Visible = False
Me("line" & i & "2").Visible = False
Me("line" & i & "3").Visible = False
End If
Next i
End Sub

Sub CreateReportQuery()
On Error GoTo Err_CreateQuery
Dim db As DAO.Database
Dim rs As DAO.Recordset
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

Set db = CurrentDb
Set qdf = db.QueryDefs("qryMonthlyIncidentsBase_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 15
FieldList = FieldList & "null as Field" & i & ","
Next i
FieldList = Left(FieldList, Len(FieldList) - 1)

strSQL = "Select " & FieldList & " From qryMonthlyIncidentsBase_Crosstab"

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

'MsgBox 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 qryCrossTabReport is based on the underlying crosstab query
qryMonthlyIncidentsBase_Crosstab.

I tried to find the person who originated the above solution to credit in
my
dbase, but I can't even find that anymore. I just downloaded and worked
from
there.

Again, I appreciate your help (you helped me a few years back through
another problem that worked real well for me). Happy New Year.
Brigitte







Crosstab queries require the explicit identification of the parameter
data
types. While in the design view, select Query->Parameters and enter
Forms![formname]![StartDate] Date/Time
Forms![formname]![EndDate] Date/Time

--
Duane Hookom
MS Access MVP


I used the solution CrosstabReport2k from the website you
recommended.
The
report is based on a query of acrosstab query, renames the fields from
the
crosstab, and code behind the report hides/shows colums/labels as
needed.
I
could figure this out. My little pesky problem is that I can't set the
column headings in the crosstab, and therefore, my crosstab won't
accept
my
data parameters in the Where clause of the Date field. It will
accept
the
absolute date values, e.g. Between #5/1/04# and #10/30/04# but not
Between
Forms![formname]![StartDate] And Forms![formname]![EndDate]
I tried to set the dates in the query underlying the crosstab, tried it
in
the Parameter fields of the crosstab and in the Where clause. The date
field
no longer exists in the query underlying the report, because the
date
has
become the column value.
I know that it has to do with column headings not being set in the
crosstab,
but when I set them, the report doesn't work any longer.
Is there a way around this.
Again, many thanks for your help.
Brigitte P.

You could use the Mth0 solution combined with some code in the On Open
event
of the report that would hide some columns that the user doesn't want
to
see.

Another method is to use the Query By Form applet at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.
You
would create a generic crosstab with the maximum number of months
as
save
the query with a name like "<Events By Month>". This would expose the
query
so your users could select any fields and set any criteria to view the
data.
The records are a couple clicks from sending to printer, html, csv,
Excel,
Word table, Word Merge, graph,...

--
Duane Hookom
MS Access MVP
--

I'm on my end. I have a crosstab query with 2 row headings (a
category
and
subcategory) and then an EventDate which results in the column going
Jan,
Feb, Mar ..... Dec. The query works fine, except it gives me all
these
empty
columns when I select less then 12 months. I know, it's an old
problem.
I tried the code that Microsoft has published, downloaded the
solution
database and copied the form and code from there, just changing
to
my
form
and query names, but it doesn't work. I suppose this is because I
have
2
row
headings instead one and deal with 12 date colums vs. the 9 they had
provided for. I added the extra columns, headers, totals to come to
the
total 15 required, but that didn't help.
I then went to the site recommended by Duane Hookom with the "Mth0"
solution. This works as long as the underlying data is static, that
is,
as
long as I select the same number of month that I have allowed for in
the
report. If I have Mth1, Mth 2, Mth 3 in the report and select the
next
time
6 month, 3 of them won't show up. I tried the Pivot Table solution,
but
that
is not what I need.
I'm about to pull out my hair; don't know how to do it, and don't
know
enough about the Microsoft VBA to fix it.
The only solution I could find, have the end user put in the values
for
the
static colums (e.g., if they select from Aug - Oct they would
have
to
type
Aug, Sep, Oct in the Column Headings query property. Is there a way
to
prompt the user for this or do I need to make the crosstab query
available
to her and hope that she doesn't forget or "mess up"?
Or, maybe a kind sould could copy the Microsoft solution, correct
for
my
extra head, col, tot rows, and email it to me. My end result needs
to
be:

Event Subevent Total Aug Sep Oct (or Jan,
Feb,
Mar) (or whatever combination of month the user needs)
Fall Slippery 12 2 4 6
Medcial Flu 3 1 2

Many thanks in advance for any help.
Brigitte P

 

[Brigitte P]

Duane, I used the query as sent by you, and it returns the 2 text column,
the total column, and the first selected month but no further columns. Also,
the first selected month as Mo0 (which is the start date month) but nothing
after this. I still would need to lable each month for what it actually is.
I suppose it is very hard to come to an anwer without having the dbase
available.

As I said, my solution from the Rogers site (CrossTabReport2K, shows some
Doctor scheduling; don't know who did the dbase but would like to know) did
work quiet well, except now I get an error in this one also when I flip
through the report pages - code stops at Me("line" & i & "2").Visible =
False (error 2465, can't find the field "line12" referred to in your
expression.).
Things run fine when I delete the following part of the code:

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
Dim i As Integer
For i = 1 To 15
If FillLabel(i) <> "" Then
Me("line" & i & "1").Visible = True
Me("line" & i & "2").Visible = True
Me("line" & i & "3").Visible = True

Else
Me("line" & i & "1").Visible = False
Me("line" & i & "2").Visible = False
Me("line" & i & "3").Visible = False

End If
Next i
End Sub

Are I'm doing any harm by deleting it? The report works without it, but will
it always be correct?

I had planned on using this solution, and ask the user for absolute date
input via a read only query that would be opened via a macro in design view
(Unique IDNumber and date only, then link that query to the crosstab). The
solution does all what I want, but I now get this error and I don't know for
sure if I just can delete the offensive code. Also, it will still not accept
the input via parameters from a form (even though the query will do fine,
it's just in the report), but this is a small price to pay if all else work.
Any thought on this, anyone.
Thanks - I'm growing years older over this one.

I would set up the crosstab (report's record source) as
Query 1: QryMonthlyIncidentsBase_Crosstab

PARAMETERS [Forms]![PGH SPECIFIC REPORTS]![txtStartDate] DateTime,
[Forms]![PGH SPECIFIC REPORTS]![txtEndDate] DateTime;
TRANSFORM Count(qryMonthlyIncidentsBase.EventEvent) AS CountOfEventEvent
SELECT qryMonthlyIncidentsBase.EventEvent,
qryMonthlyIncidentsBase.EventSubcategory,
Count(qryMonthlyIncidentsBase.EventEvent) AS [Total Of EventEvent]
FROM qryMonthlyIncidentsBase
WHERE (((qryMonthlyIncidentsBase.EventDate) Between [Forms]![PGH SPECIFIC
REPORTS]![txtStartDate] And [Forms]![PGH SPECIFIC REPORTS]![txtEndDate]))
GROUP BY qryMonthlyIncidentsBase.EventEvent,
qryMonthlyIncidentsBase.EventSubcategory
ORDER BY qryMonthlyIncidentsBase.EventEvent,
qryMonthlyIncidentsBase.EventSubcategory
PIVOT "M" & DateDiff("m",[EventDate],
Forms![PGH SPECIFIC REPORTS]!txtStartDate) IN
("M0","M1","M2","M3",..."M11");

If txtEndDate is less than 12 months past txtStartDate, then the last month
columns will be blank/null. Other columns beginning with M0 should have data
values. Base your report on this query.

You can set column headings/labels in the report with text boxes with
control sources like:
=DateAdd("m",0,Forms![PGH SPECIFIC REPORTS]!txtStartDate)
=DateAdd("m",1,Forms![PGH SPECIFIC REPORTS]!txtStartDate)
=DateAdd("m",2,Forms![PGH SPECIFIC REPORTS]!txtStartDate)
=...
You can use code in the On Format event of the section containing the
headings to make some invisible based on the difference between the entered
start and end dates.
--
Duane Hookom
MS Access MVP

The solution is from Rogers Access and the report runs fine when I set the
query with absolute values. However, the report comes up blank when I use
the form parameters. Following is the SQL from the Crosstab and second
from
the query that is based on the crosstab:

Query 1: QryMonthlyIncidentsBase_Crosstab

PARAMETERS [Forms]![PGH SPECIFIC REPORTS]![txtStartDate] DateTime,
[Forms]![PGH SPECIFIC REPORTS]![txtEndDate] DateTime;
TRANSFORM Count(qryMonthlyIncidentsBase.EventEvent) AS CountOfEventEvent
SELECT qryMonthlyIncidentsBase.EventEvent,
qryMonthlyIncidentsBase.EventSubcategory,
Count(qryMonthlyIncidentsBase.EventEvent) AS [Total Of EventEvent]
FROM qryMonthlyIncidentsBase
WHERE (((qryMonthlyIncidentsBase.EventDate) Between [Forms]![PGH SPECIFIC
REPORTS]![txtStartDate] And [Forms]![PGH SPECIFIC REPORTS]![txtEndDate]))
GROUP BY qryMonthlyIncidentsBase.EventEvent,
qryMonthlyIncidentsBase.EventSubcategory
ORDER BY qryMonthlyIncidentsBase.EventEvent,
qryMonthlyIncidentsBase.EventSubcategory
PIVOT Format([EventDate],"mm ");

Query2 - The one the report is bound to: qryCrosstabReport

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, null AS Field10, null AS Field11, null AS Field12, null AS
Field13, null AS Field14, null AS Field15
FROM qryMonthlyIncidentsBase_Crosstab;

Thanks for being so patient with me. Working with someone like you shows
me
how little I know even though I have developed many databases over the
years, and they run fine once I worked my way through with the help of the
user groups. I do this in addition to my main job, and I know only the
very
basics of programming. I used to know Basic for DOS (can you tell how
ancient I am and that helps. But I can do only very, very little VBA and
practically to SQL, even though I can understand both basically. Wouldn't
know what to do without the user groups. Your folks are great.
Brigitte

If you are using a solution from Rogers Access or Corporate Technologies
(invisibleinc.com) then I probably created it. I don't know which
solution
you are using. I don't think you need to open any recordset/querydef in code
since your columns of the crosstab can be determined based on the start and
end dates. It would help if you shared your SQL view.

--
Duane Hookom
MS Access MVP


Duane,
The parameter setting worked just fine, even though I had to go to SQL
view
to get the brackets right. However, I have to ask for one more favor.
In
the
report that works for me, the code obviously has not set the parameter
definition. I tried to get it together by taking snippets of code from
here
and there, but I can't get it. If you don't mind helping me through the
last
step then I can apply this to many other crosstab reports to come.
Following
is the code that makes the report work:

My parameters are: Forms![PGH SPECIFIC REPORTS]!txtStartDate and
Forms![PGH
SPECIFIC REPORTS]!txtEndDate


Option Compare Database
Option Explicit
Dim ReportLabel(16) As String

Private Sub Report_Open(Cancel As Integer)
DoCmd.Maximize
Dim i As Integer
For i = 0 To 16
ReportLabel(i) = ""
Next i
Call CreateReportQuery
End Sub

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
Dim i As Integer
For i = 1 To 16
If FillLabel(i) <> "" Then
Me("line" & i & "1").Visible = True
Me("line" & i & "2").Visible = True
Me("line" & i & "3").Visible = True
Else
Me("line" & i & "1").Visible = False
Me("line" & i & "2").Visible = False
Me("line" & i & "3").Visible = False
End If
Next i
End Sub

Sub CreateReportQuery()
On Error GoTo Err_CreateQuery
Dim db As DAO.Database
Dim rs As DAO.Recordset
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

Set db = CurrentDb
Set qdf = db.QueryDefs("qryMonthlyIncidentsBase_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 15
FieldList = FieldList & "null as Field" & i & ","
Next i
FieldList = Left(FieldList, Len(FieldList) - 1)

strSQL = "Select " & FieldList & " From qryMonthlyIncidentsBase_Crosstab"

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

'MsgBox 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 qryCrossTabReport is based on the underlying crosstab query
qryMonthlyIncidentsBase_Crosstab.

I tried to find the person who originated the above solution to

credit
in

my
dbase, but I can't even find that anymore. I just downloaded and worked
from
there.

Again, I appreciate your help (you helped me a few years back through
another problem that worked real well for me). Happy New Year.
Brigitte







Crosstab queries require the explicit identification of the parameter
data
types. While in the design view, select Query->Parameters and enter
Forms![formname]![StartDate] Date/Time
Forms![formname]![EndDate] Date/Time

--
Duane Hookom
MS Access MVP


I used the solution CrosstabReport2k from the website you
recommended.
The
report is based on a query of acrosstab query, renames the fields from
the
crosstab, and code behind the report hides/shows colums/labels as
needed.
I
could figure this out. My little pesky problem is that I can't set the
column headings in the crosstab, and therefore, my crosstab won't
accept
my
data parameters in the Where clause of the Date field. It will
accept
the
absolute date values, e.g. Between #5/1/04# and #10/30/04# but not
Between
Forms![formname]![StartDate] And Forms![formname]![EndDate]
I tried to set the dates in the query underlying the crosstab,

tried
it

in
the Parameter fields of the crosstab and in the Where clause. The date
field
no longer exists in the query underlying the report, because the
date
has
become the column value.
I know that it has to do with column headings not being set in the
crosstab,
but when I set them, the report doesn't work any longer.
Is there a way around this.
Again, many thanks for your help.
Brigitte P.

You could use the Mth0 solution combined with some code in the On Open
event
of the report that would hide some columns that the user doesn't want
to
see.

Another method is to use the Query By Form applet at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.
You
would create a generic crosstab with the maximum number of months
as
save
the query with a name like "<Events By Month>". This would expose the
query
so your users could select any fields and set any criteria to

view
the

data.
The records are a couple clicks from sending to printer, html, csv,
Excel,
Word table, Word Merge, graph,...

--
Duane Hookom
MS Access MVP
--

I'm on my end. I have a crosstab query with 2 row headings (a
category
and
subcategory) and then an EventDate which results in the column going
Jan,
Feb, Mar ..... Dec. The query works fine, except it gives me all
these
empty
columns when I select less then 12 months. I know, it's an old
problem.
I tried the code that Microsoft has published, downloaded the
solution
database and copied the form and code from there, just changing
to
my
form
and query names, but it doesn't work. I suppose this is because I
have
2
row
headings instead one and deal with 12 date colums vs. the 9

they
had

provided for. I added the extra columns, headers, totals to

come
to

the
total 15 required, but that didn't help.
I then went to the site recommended by Duane Hookom with the "Mth0"
solution. This works as long as the underlying data is static, that
is,
as
long as I select the same number of month that I have allowed

for
in

the
report. If I have Mth1, Mth 2, Mth 3 in the report and select the
next
time
6 month, 3 of them won't show up. I tried the Pivot Table solution,
but
that
is not what I need.
I'm about to pull out my hair; don't know how to do it, and don't
know
enough about the Microsoft VBA to fix it.
The only solution I could find, have the end user put in the values
for
the
static colums (e.g., if they select from Aug - Oct they would
have
to
type
Aug, Sep, Oct in the Column Headings query property. Is there a way
to
prompt the user for this or do I need to make the crosstab query
available
to her and hope that she doesn't forget or "mess up"?
Or, maybe a kind sould could copy the Microsoft solution, correct
for
my
extra head, col, tot rows, and email it to me. My end result needs
to
be:

Event Subevent Total Aug Sep Oct (or Jan,
Feb,
Mar) (or whatever combination of month the user needs)
Fall Slippery 12 2 4 6
Medcial Flu 3 1 2

Many thanks in advance for any help.
Brigitte P

 

[Brigitte P]

Duane, I used the query as sent by you, and it returns the 2 text column,
the total column, and the first selected month but no further columns. Also,
the first selected month as Mo0 (which is the start date month) but nothing
after this. I still would need to lable each month for what it actually is.
I suppose it is very hard to come to an anwer without having the dbase
available.

As I said, my solution from the Rogers site (CrossTabReport2K, shows some
Doctor scheduling; don't know who did the dbase but would like to know -
found it at
http://www.rogersaccesslibrary.com/download3.asp?SampleName=CrossTabReport.mdb)
did work quiet well, except now I get an error in this one also when I flip
through the report pages - code stops at Me("line" & i & "2").Visible =
False (error 2465, can't find the field "line12" referred to in your
expression.). Things run fine when I delete the following part of the code:

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
Dim i As Integer
For i = 1 To 15
If FillLabel(i) <> "" Then
Me("line" & i & "1").Visible = True
Me("line" & i & "2").Visible = True
Me("line" & i & "3").Visible = True

Else
Me("line" & i & "1").Visible = False
Me("line" & i & "2").Visible = False
Me("line" & i & "3").Visible = False

End If
Next i
End Sub

Are I'm doing any harm by deleting it? The report works without it, but will
it always be correct?

I had planned on using this solution, and ask the user for absolute date
input via a read only query that would be opened via a macro in design view
(Unique IDNumber and date only, then link that query to the crosstab). The
solution does all what I want, but I now get this error and I don't know for
sure if I just can delete the offensive code. Also, it will still not accept
the input via parameters from a form (even though the query will do fine,
the problem
is just in the report), but this is a small price to pay if all else work
and if I can't figure out how to code the parameter definition in the report
part of the project. Any thought on this, anyone.
Thanks - I'm growing years older over this one and don't get much slepp
anymore.
Brigitte

I would set up the crosstab (report's record source) as
Query 1: QryMonthlyIncidentsBase_Crosstab

PARAMETERS [Forms]![PGH SPECIFIC REPORTS]![txtStartDate] DateTime,
[Forms]![PGH SPECIFIC REPORTS]![txtEndDate] DateTime;
TRANSFORM Count(qryMonthlyIncidentsBase.EventEvent) AS CountOfEventEvent
SELECT qryMonthlyIncidentsBase.EventEvent,
qryMonthlyIncidentsBase.EventSubcategory,
Count(qryMonthlyIncidentsBase.EventEvent) AS [Total Of EventEvent]
FROM qryMonthlyIncidentsBase
WHERE (((qryMonthlyIncidentsBase.EventDate) Between [Forms]![PGH SPECIFIC
REPORTS]![txtStartDate] And [Forms]![PGH SPECIFIC REPORTS]![txtEndDate]))
GROUP BY qryMonthlyIncidentsBase.EventEvent,
qryMonthlyIncidentsBase.EventSubcategory
ORDER BY qryMonthlyIncidentsBase.EventEvent,
qryMonthlyIncidentsBase.EventSubcategory
PIVOT "M" & DateDiff("m",[EventDate],
Forms![PGH SPECIFIC REPORTS]!txtStartDate) IN
("M0","M1","M2","M3",..."M11");

If txtEndDate is less than 12 months past txtStartDate, then the last month
columns will be blank/null. Other columns beginning with M0 should have data
values. Base your report on this query.

You can set column headings/labels in the report with text boxes with
control sources like:
=DateAdd("m",0,Forms![PGH SPECIFIC REPORTS]!txtStartDate)
=DateAdd("m",1,Forms![PGH SPECIFIC REPORTS]!txtStartDate)
=DateAdd("m",2,Forms![PGH SPECIFIC REPORTS]!txtStartDate)
=...
You can use code in the On Format event of the section containing the
headings to make some invisible based on the difference between the entered
start and end dates.
--
Duane Hookom
MS Access MVP

The solution is from Rogers Access and the report runs fine when I set the
query with absolute values. However, the report comes up blank when I use
the form parameters. Following is the SQL from the Crosstab and second
from
the query that is based on the crosstab:

Query 1: QryMonthlyIncidentsBase_Crosstab

PARAMETERS [Forms]![PGH SPECIFIC REPORTS]![txtStartDate] DateTime,
[Forms]![PGH SPECIFIC REPORTS]![txtEndDate] DateTime;
TRANSFORM Count(qryMonthlyIncidentsBase.EventEvent) AS CountOfEventEvent
SELECT qryMonthlyIncidentsBase.EventEvent,
qryMonthlyIncidentsBase.EventSubcategory,
Count(qryMonthlyIncidentsBase.EventEvent) AS [Total Of EventEvent]
FROM qryMonthlyIncidentsBase
WHERE (((qryMonthlyIncidentsBase.EventDate) Between [Forms]![PGH SPECIFIC
REPORTS]![txtStartDate] And [Forms]![PGH SPECIFIC REPORTS]![txtEndDate]))
GROUP BY qryMonthlyIncidentsBase.EventEvent,
qryMonthlyIncidentsBase.EventSubcategory
ORDER BY qryMonthlyIncidentsBase.EventEvent,
qryMonthlyIncidentsBase.EventSubcategory
PIVOT Format([EventDate],"mm ");

Query2 - The one the report is bound to: qryCrosstabReport

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, null AS Field10, null AS Field11, null AS Field12, null AS
Field13, null AS Field14, null AS Field15
FROM qryMonthlyIncidentsBase_Crosstab;

Thanks for being so patient with me. Working with someone like you shows
me
how little I know even though I have developed many databases over the
years, and they run fine once I worked my way through with the help of the
user groups. I do this in addition to my main job, and I know only the
very
basics of programming. I used to know Basic for DOS (can you tell how
ancient I am and that helps. But I can do only very, very little VBA and
practically to SQL, even though I can understand both basically. Wouldn't
know what to do without the user groups. Your folks are great.
Brigitte

If you are using a solution from Rogers Access or Corporate Technologies
(invisibleinc.com) then I probably created it. I don't know which
solution
you are using. I don't think you need to open any recordset/querydef in code
since your columns of the crosstab can be determined based on the start and
end dates. It would help if you shared your SQL view.

--
Duane Hookom
MS Access MVP


Duane,
The parameter setting worked just fine, even though I had to go to SQL
view
to get the brackets right. However, I have to ask for one more favor.
In
the
report that works for me, the code obviously has not set the parameter
definition. I tried to get it together by taking snippets of code from
here
and there, but I can't get it. If you don't mind helping me through the
last
step then I can apply this to many other crosstab reports to come.
Following
is the code that makes the report work:

My parameters are: Forms![PGH SPECIFIC REPORTS]!txtStartDate and
Forms![PGH
SPECIFIC REPORTS]!txtEndDate


Option Compare Database
Option Explicit
Dim ReportLabel(16) As String

Private Sub Report_Open(Cancel As Integer)
DoCmd.Maximize
Dim i As Integer
For i = 0 To 16
ReportLabel(i) = ""
Next i
Call CreateReportQuery
End Sub

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
Dim i As Integer
For i = 1 To 16
If FillLabel(i) <> "" Then
Me("line" & i & "1").Visible = True
Me("line" & i & "2").Visible = True
Me("line" & i & "3").Visible = True
Else
Me("line" & i & "1").Visible = False
Me("line" & i & "2").Visible = False
Me("line" & i & "3").Visible = False
End If
Next i
End Sub

Sub CreateReportQuery()
On Error GoTo Err_CreateQuery
Dim db As DAO.Database
Dim rs As DAO.Recordset
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

Set db = CurrentDb
Set qdf = db.QueryDefs("qryMonthlyIncidentsBase_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 15
FieldList = FieldList & "null as Field" & i & ","
Next i
FieldList = Left(FieldList, Len(FieldList) - 1)

strSQL = "Select " & FieldList & " From qryMonthlyIncidentsBase_Crosstab"

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

'MsgBox 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 qryCrossTabReport is based on the underlying crosstab query
qryMonthlyIncidentsBase_Crosstab.

I tried to find the person who originated the above solution to

credit
in

my
dbase, but I can't even find that anymore. I just downloaded and worked
from
there.

Again, I appreciate your help (you helped me a few years back through
another problem that worked real well for me). Happy New Year.
Brigitte







Crosstab queries require the explicit identification of the parameter
data
types. While in the design view, select Query->Parameters and enter
Forms![formname]![StartDate] Date/Time
Forms![formname]![EndDate] Date/Time

--
Duane Hookom
MS Access MVP


I used the solution CrosstabReport2k from the website you
recommended.
The
report is based on a query of acrosstab query, renames the fields from
the
crosstab, and code behind the report hides/shows colums/labels as
needed.
I
could figure this out. My little pesky problem is that I can't set the
column headings in the crosstab, and therefore, my crosstab won't
accept
my
data parameters in the Where clause of the Date field. It will
accept
the
absolute date values, e.g. Between #5/1/04# and #10/30/04# but not
Between
Forms![formname]![StartDate] And Forms![formname]![EndDate]
I tried to set the dates in the query underlying the crosstab,

tried
it

in
the Parameter fields of the crosstab and in the Where clause. The date
field
no longer exists in the query underlying the report, because the
date
has
become the column value.
I know that it has to do with column headings not being set in the
crosstab,
but when I set them, the report doesn't work any longer.
Is there a way around this.
Again, many thanks for your help.
Brigitte P.

You could use the Mth0 solution combined with some code in the On Open
event
of the report that would hide some columns that the user doesn't want
to
see.

Another method is to use the Query By Form applet at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.
You
would create a generic crosstab with the maximum number of months
as
save
the query with a name like "<Events By Month>". This would expose the
query
so your users could select any fields and set any criteria to

view
the

data.
The records are a couple clicks from sending to printer, html, csv,
Excel,
Word table, Word Merge, graph,...

--
Duane Hookom
MS Access MVP
--

I'm on my end. I have a crosstab query with 2 row headings (a
category
and
subcategory) and then an EventDate which results in the column going
Jan,
Feb, Mar ..... Dec. The query works fine, except it gives me all
these
empty
columns when I select less then 12 months. I know, it's an old
problem.
I tried the code that Microsoft has published, downloaded the
solution
database and copied the form and code from there, just changing
to
my
form
and query names, but it doesn't work. I suppose this is because I
have
2
row
headings instead one and deal with 12 date colums vs. the 9

they
had

provided for. I added the extra columns, headers, totals to

come
to

the
total 15 required, but that didn't help.
I then went to the site recommended by Duane Hookom with the "Mth0"
solution. This works as long as the underlying data is static, that
is,
as
long as I select the same number of month that I have allowed

for
in

the
report. If I have Mth1, Mth 2, Mth 3 in the report and select the
next
time
6 month, 3 of them won't show up. I tried the Pivot Table solution,
but
that
is not what I need.
I'm about to pull out my hair; don't know how to do it, and don't
know
enough about the Microsoft VBA to fix it.
The only solution I could find, have the end user put in the values
for
the
static colums (e.g., if they select from Aug - Oct they would
have
to
type
Aug, Sep, Oct in the Column Headings query property. Is there a way
to
prompt the user for this or do I need to make the crosstab query
available
to her and hope that she doesn't forget or "mess up"?
Or, maybe a kind sould could copy the Microsoft solution, correct
for
my
extra head, col, tot rows, and email it to me. My end result needs
to
be:

Event Subevent Total Aug Sep Oct (or Jan,
Feb,
Mar) (or whatever combination of month the user needs)
Fall Slippery 12 2 4 6
Medcial Flu 3 1 2

Many thanks in advance for any help.
Brigitte P

 

[Brigitte P]

Duane, I used the query as sent by you, and it returns the 2 text column,
the total column, and the first selected month but no further columns. Also,
the first selected month as Mo0 (which is the start date month) but nothing
after this. I still would need to lable each month for what it actually is.
I suppose it is very hard to come to an anwer without having the dbase
available.

As I said, my solution from the Rogers site (CrossTabReport2K, shows some
Doctor scheduling; don't know who did the dbase but would like to know -
found it at
http://www.rogersaccesslibrary.com/download3.asp?SampleName=CrossTabReport.mdb)
did work quiet well, except now I get an error in this one also when I flip
through the report pages - code stops at Me("line" & i & "2").Visible =
False (error 2465, can't find the field "line12" referred to in your
expression.). Things run fine when I delete the following part of the code:

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
Dim i As Integer
For i = 1 To 15
If FillLabel(i) <> "" Then
Me("line" & i & "1").Visible = True
Me("line" & i & "2").Visible = True
Me("line" & i & "3").Visible = True

Else
Me("line" & i & "1").Visible = False
Me("line" & i & "2").Visible = False
Me("line" & i & "3").Visible = False

End If
Next i
End Sub

Are I'm doing any harm by deleting it? The report works without it, but will
it always be correct?

I had planned on using this solution, and ask the user for absolute date
input via a read only query that would be opened via a macro in design view
(Unique IDNumber and date only, then link that query to the crosstab). The
solution does all what I want, but I now get this error and I don't know for
sure if I just can delete the offensive code. Also, it will still not accept
the input via parameters from a form (even though the query will do fine,
the problem
is just in the report), but this is a small price to pay if all else work
and if I can't figure out how to code the parameter definition in the report
part of the project. Any thought on this, anyone.
Thanks - I'm growing years older over this one and don't get much slepp
anymore.
Brigitte

I would set up the crosstab (report's record source) as
Query 1: QryMonthlyIncidentsBase_Crosstab

PARAMETERS [Forms]![PGH SPECIFIC REPORTS]![txtStartDate] DateTime,
[Forms]![PGH SPECIFIC REPORTS]![txtEndDate] DateTime;
TRANSFORM Count(qryMonthlyIncidentsBase.EventEvent) AS CountOfEventEvent
SELECT qryMonthlyIncidentsBase.EventEvent,
qryMonthlyIncidentsBase.EventSubcategory,
Count(qryMonthlyIncidentsBase.EventEvent) AS [Total Of EventEvent]
FROM qryMonthlyIncidentsBase
WHERE (((qryMonthlyIncidentsBase.EventDate) Between [Forms]![PGH SPECIFIC
REPORTS]![txtStartDate] And [Forms]![PGH SPECIFIC REPORTS]![txtEndDate]))
GROUP BY qryMonthlyIncidentsBase.EventEvent,
qryMonthlyIncidentsBase.EventSubcategory
ORDER BY qryMonthlyIncidentsBase.EventEvent,
qryMonthlyIncidentsBase.EventSubcategory
PIVOT "M" & DateDiff("m",[EventDate],
Forms![PGH SPECIFIC REPORTS]!txtStartDate) IN
("M0","M1","M2","M3",..."M11");

If txtEndDate is less than 12 months past txtStartDate, then the last month
columns will be blank/null. Other columns beginning with M0 should have data
values. Base your report on this query.

You can set column headings/labels in the report with text boxes with
control sources like:
=DateAdd("m",0,Forms![PGH SPECIFIC REPORTS]!txtStartDate)
=DateAdd("m",1,Forms![PGH SPECIFIC REPORTS]!txtStartDate)
=DateAdd("m",2,Forms![PGH SPECIFIC REPORTS]!txtStartDate)
=...
You can use code in the On Format event of the section containing the
headings to make some invisible based on the difference between the entered
start and end dates.
--
Duane Hookom
MS Access MVP

The solution is from Rogers Access and the report runs fine when I set the
query with absolute values. However, the report comes up blank when I use
the form parameters. Following is the SQL from the Crosstab and second
from
the query that is based on the crosstab:

Query 1: QryMonthlyIncidentsBase_Crosstab

PARAMETERS [Forms]![PGH SPECIFIC REPORTS]![txtStartDate] DateTime,
[Forms]![PGH SPECIFIC REPORTS]![txtEndDate] DateTime;
TRANSFORM Count(qryMonthlyIncidentsBase.EventEvent) AS CountOfEventEvent
SELECT qryMonthlyIncidentsBase.EventEvent,
qryMonthlyIncidentsBase.EventSubcategory,
Count(qryMonthlyIncidentsBase.EventEvent) AS [Total Of EventEvent]
FROM qryMonthlyIncidentsBase
WHERE (((qryMonthlyIncidentsBase.EventDate) Between [Forms]![PGH SPECIFIC
REPORTS]![txtStartDate] And [Forms]![PGH SPECIFIC REPORTS]![txtEndDate]))
GROUP BY qryMonthlyIncidentsBase.EventEvent,
qryMonthlyIncidentsBase.EventSubcategory
ORDER BY qryMonthlyIncidentsBase.EventEvent,
qryMonthlyIncidentsBase.EventSubcategory
PIVOT Format([EventDate],"mm ");

Query2 - The one the report is bound to: qryCrosstabReport

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, null AS Field10, null AS Field11, null AS Field12, null AS
Field13, null AS Field14, null AS Field15
FROM qryMonthlyIncidentsBase_Crosstab;

Thanks for being so patient with me. Working with someone like you shows
me
how little I know even though I have developed many databases over the
years, and they run fine once I worked my way through with the help of the
user groups. I do this in addition to my main job, and I know only the
very
basics of programming. I used to know Basic for DOS (can you tell how
ancient I am and that helps. But I can do only very, very little VBA and
practically to SQL, even though I can understand both basically. Wouldn't
know what to do without the user groups. Your folks are great.
Brigitte

If you are using a solution from Rogers Access or Corporate Technologies
(invisibleinc.com) then I probably created it. I don't know which
solution
you are using. I don't think you need to open any recordset/querydef in code
since your columns of the crosstab can be determined based on the start and
end dates. It would help if you shared your SQL view.

--
Duane Hookom
MS Access MVP


Duane,
The parameter setting worked just fine, even though I had to go to SQL
view
to get the brackets right. However, I have to ask for one more favor.
In
the
report that works for me, the code obviously has not set the parameter
definition. I tried to get it together by taking snippets of code from
here
and there, but I can't get it. If you don't mind helping me through the
last
step then I can apply this to many other crosstab reports to come.
Following
is the code that makes the report work:

My parameters are: Forms![PGH SPECIFIC REPORTS]!txtStartDate and
Forms![PGH
SPECIFIC REPORTS]!txtEndDate


Option Compare Database
Option Explicit
Dim ReportLabel(16) As String

Private Sub Report_Open(Cancel As Integer)
DoCmd.Maximize
Dim i As Integer
For i = 0 To 16
ReportLabel(i) = ""
Next i
Call CreateReportQuery
End Sub

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
Dim i As Integer
For i = 1 To 16
If FillLabel(i) <> "" Then
Me("line" & i & "1").Visible = True
Me("line" & i & "2").Visible = True
Me("line" & i & "3").Visible = True
Else
Me("line" & i & "1").Visible = False
Me("line" & i & "2").Visible = False
Me("line" & i & "3").Visible = False
End If
Next i
End Sub

Sub CreateReportQuery()
On Error GoTo Err_CreateQuery
Dim db As DAO.Database
Dim rs As DAO.Recordset
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

Set db = CurrentDb
Set qdf = db.QueryDefs("qryMonthlyIncidentsBase_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 15
FieldList = FieldList & "null as Field" & i & ","
Next i
FieldList = Left(FieldList, Len(FieldList) - 1)

strSQL = "Select " & FieldList & " From qryMonthlyIncidentsBase_Crosstab"

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

'MsgBox 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 qryCrossTabReport is based on the underlying crosstab query
qryMonthlyIncidentsBase_Crosstab.

I tried to find the person who originated the above solution to

credit
in

my
dbase, but I can't even find that anymore. I just downloaded and worked
from
there.

Again, I appreciate your help (you helped me a few years back through
another problem that worked real well for me). Happy New Year.
Brigitte







Crosstab queries require the explicit identification of the parameter
data
types. While in the design view, select Query->Parameters and enter
Forms![formname]![StartDate] Date/Time
Forms![formname]![EndDate] Date/Time

--
Duane Hookom
MS Access MVP


I used the solution CrosstabReport2k from the website you
recommended.
The
report is based on a query of acrosstab query, renames the fields from
the
crosstab, and code behind the report hides/shows colums/labels as
needed.
I
could figure this out. My little pesky problem is that I can't set the
column headings in the crosstab, and therefore, my crosstab won't
accept
my
data parameters in the Where clause of the Date field. It will
accept
the
absolute date values, e.g. Between #5/1/04# and #10/30/04# but not
Between
Forms![formname]![StartDate] And Forms![formname]![EndDate]
I tried to set the dates in the query underlying the crosstab,

tried
it

in
the Parameter fields of the crosstab and in the Where clause. The date
field
no longer exists in the query underlying the report, because the
date
has
become the column value.
I know that it has to do with column headings not being set in the
crosstab,
but when I set them, the report doesn't work any longer.
Is there a way around this.
Again, many thanks for your help.
Brigitte P.

You could use the Mth0 solution combined with some code in the On Open
event
of the report that would hide some columns that the user doesn't want
to
see.

Another method is to use the Query By Form applet at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.
You
would create a generic crosstab with the maximum number of months
as
save
the query with a name like "<Events By Month>". This would expose the
query
so your users could select any fields and set any criteria to

view
the

data.
The records are a couple clicks from sending to printer, html, csv,
Excel,
Word table, Word Merge, graph,...

--
Duane Hookom
MS Access MVP
--

I'm on my end. I have a crosstab query with 2 row headings (a
category
and
subcategory) and then an EventDate which results in the column going
Jan,
Feb, Mar ..... Dec. The query works fine, except it gives me all
these
empty
columns when I select less then 12 months. I know, it's an old
problem.
I tried the code that Microsoft has published, downloaded the
solution
database and copied the form and code from there, just changing
to
my
form
and query names, but it doesn't work. I suppose this is because I
have
2
row
headings instead one and deal with 12 date colums vs. the 9

they
had

provided for. I added the extra columns, headers, totals to

come
to

the
total 15 required, but that didn't help.
I then went to the site recommended by Duane Hookom with the "Mth0"
solution. This works as long as the underlying data is static, that
is,
as
long as I select the same number of month that I have allowed

for
in

the
report. If I have Mth1, Mth 2, Mth 3 in the report and select the
next
time
6 month, 3 of them won't show up. I tried the Pivot Table solution,
but
that
is not what I need.
I'm about to pull out my hair; don't know how to do it, and don't
know
enough about the Microsoft VBA to fix it.
The only solution I could find, have the end user put in the values
for
the
static colums (e.g., if they select from Aug - Oct they would
have
to
type
Aug, Sep, Oct in the Column Headings query property. Is there a way
to
prompt the user for this or do I need to make the crosstab query
available
to her and hope that she doesn't forget or "mess up"?
Or, maybe a kind sould could copy the Microsoft solution, correct
for
my
extra head, col, tot rows, and email it to me. My end result needs
to
be:

Event Subevent Total Aug Sep Oct (or Jan,
Feb,
Mar) (or whatever combination of month the user needs)
Fall Slippery 12 2 4 6
Medcial Flu 3 1 2

Many thanks in advance for any help.
Brigitte P

 

[Duane Hookom]

I don't want to find out what is going on with Roger's solution. He does
read through these NGs and may find this thread. My solution is based on
this solution:
=================
Try not to use "absolute" column headings for dates. You could possibly use
"relative" dates. For instance:
Form: frmA
Text Box: txtEndDate
Table: tblSales
Field: SaleDate
You want to show 12 months of sales in columns of a crosstab report.
Set the with menuing: Query|Parameter
Forms!frmA!txtEndDate Date/Time

Use this expression for your Column Headings:
ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA!txtEndDate)

This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales from the
same month as the ending date on your form. Mth1 is the previous month etc.

Set your queries Column Headings property to:
"Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
Build your report based on these "relative" months. If you need column
labels in your report, use text boxes with control sources of:
=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)
...
This solution requires no code and will run fairly quickly.
====================
I don't think it would be expecting to much from a user to have a 12 mth
report and a 6 mth report,...

 

[Brigitte P]

Sorry about the repeated postings; my email sometimes does very strange
things.

Duane,
I tried this solution very early on and it does work, except it didn't hide
the unused columns. However, it's a good idea to set up one 12 month and one
6 month report so things don't look too clumsy with a little creativity in
the report layout. I can set the buttons next to each other so the user can
pick; it is not expecting too much for the user to think about this. The
database runs for data entry for our whole hospital, and for review with a
few selected users. However, the reports are run by only one person who is
pretty good with those tings, so all should work out just fine.
THANKS for your patience with this; you were of great help and I will
remember for future use how to do this. One would think that Microsoft would
come up with a dynamic crosstab query report wizzard since this is such a
common question with users. Maybe, just maybe, Microsoft becomes inspired.
Inserting a pivot table in a report works but looks really, really bad.
Thanks again.
Brigitte

I don't want to find out what is going on with Roger's solution. He does
read through these NGs and may find this thread. My solution is based on
this solution:
=================
Try not to use "absolute" column headings for dates. You could possibly use
"relative" dates. For instance:
Form: frmA
Text Box: txtEndDate
Table: tblSales
Field: SaleDate
You want to show 12 months of sales in columns of a crosstab report.
Set the with menuing: Query|Parameter
Forms!frmA!txtEndDate Date/Time

Use this expression for your Column Headings:
ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA!txtEndDate)

This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales from the
same month as the ending date on your form. Mth1 is the previous month etc.

Set your queries Column Headings property to:
"Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
Build your report based on these "relative" months. If you need column
labels in your report, use text boxes with control sources of:
=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)
...
This solution requires no code and will run fairly quickly.
====================
I don't think it would be expecting to much from a user to have a 12 mth
report and a 6 mth report,...

--
Duane Hookom
MS Access MVP

Duane, I used the query as sent by you, and it returns the 2 text column,
the total column, and the first selected month but no further columns.
Also,
the first selected month as Mo0 (which is the start date month) but
nothing
after this. I still would need to lable each month for what it actually
is.
I suppose it is very hard to come to an anwer without having the dbase
available.

As I said, my solution from the Rogers site (CrossTabReport2K, shows some
Doctor scheduling; don't know who did the dbase but would like to know)
did
work quiet well, except now I get an error in this one also when I flip
through the report pages - code stops at Me("line" & i & "2").Visible =
False (error 2465, can't find the field "line12" referred to in your
expression.).
Things run fine when I delete the following part of the code:

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
Dim i As Integer
For i = 1 To 15
If FillLabel(i) <> "" Then
Me("line" & i & "1").Visible = True
Me("line" & i & "2").Visible = True
Me("line" & i & "3").Visible = True

Else
Me("line" & i & "1").Visible = False
Me("line" & i & "2").Visible = False
Me("line" & i & "3").Visible = False

End If
Next i
End Sub

Are I'm doing any harm by deleting it? The report works without it, but
will
it always be correct?

I had planned on using this solution, and ask the user for absolute date
input via a read only query that would be opened via a macro in design
view
(Unique IDNumber and date only, then link that query to the crosstab).
The
solution does all what I want, but I now get this error and I don't know
for
sure if I just can delete the offensive code. Also, it will still not
accept
the input via parameters from a form (even though the query will do fine,
it's just in the report), but this is a small price to pay if all else
work.
Any thought on this, anyone.
Thanks - I'm growing years older over this one.

 

[Duane Hookom]

If you really needed to hide columns, you could use the most recent solution
and hide column titles based on the beginning and ending dates.

There is another solution for dynamic crosstabs at Roger's site under my
stuff http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.
With date increments as column headings, I would always choose the other
solution.

--
Duane Hookom
MS Access MVP

Sorry about the repeated postings; my email sometimes does very strange
things.

Duane,
I tried this solution very early on and it does work, except it didn't
hide
the unused columns. However, it's a good idea to set up one 12 month and
one
6 month report so things don't look too clumsy with a little creativity in
the report layout. I can set the buttons next to each other so the user
can
pick; it is not expecting too much for the user to think about this. The
database runs for data entry for our whole hospital, and for review with a
few selected users. However, the reports are run by only one person who is
pretty good with those tings, so all should work out just fine.
THANKS for your patience with this; you were of great help and I will
remember for future use how to do this. One would think that Microsoft
would
come up with a dynamic crosstab query report wizzard since this is such a
common question with users. Maybe, just maybe, Microsoft becomes inspired.
Inserting a pivot table in a report works but looks really, really bad.
Thanks again.
Brigitte

I don't want to find out what is going on with Roger's solution. He does
read through these NGs and may find this thread. My solution is based on
this solution:
=================
Try not to use "absolute" column headings for dates. You could possibly use
"relative" dates. For instance:
Form: frmA
Text Box: txtEndDate
Table: tblSales
Field: SaleDate
You want to show 12 months of sales in columns of a crosstab report.
Set the with menuing: Query|Parameter
Forms!frmA!txtEndDate Date/Time

Use this expression for your Column Headings:
ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA!txtEndDate)

This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales from the
same month as the ending date on your form. Mth1 is the previous month etc.

Set your queries Column Headings property to:
"Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
Build your report based on these "relative" months. If you need column
labels in your report, use text boxes with control sources of:
=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)
...
This solution requires no code and will run fairly quickly.
====================
I don't think it would be expecting to much from a user to have a 12 mth
report and a 6 mth report,...

--
Duane Hookom
MS Access MVP

Duane, I used the query as sent by you, and it returns the 2 text column,
the total column, and the first selected month but no further columns.
Also,
the first selected month as Mo0 (which is the start date month) but
nothing
after this. I still would need to lable each month for what it actually
is.
I suppose it is very hard to come to an anwer without having the dbase
available.

As I said, my solution from the Rogers site (CrossTabReport2K, shows some
Doctor scheduling; don't know who did the dbase but would like to know)
did
work quiet well, except now I get an error in this one also when I flip
through the report pages - code stops at Me("line" & i & "2").Visible
=
False (error 2465, can't find the field "line12" referred to in your
expression.).
Things run fine when I delete the following part of the code:

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
Dim i As Integer
For i = 1 To 15
If FillLabel(i) <> "" Then
Me("line" & i & "1").Visible = True
Me("line" & i & "2").Visible = True
Me("line" & i & "3").Visible = True

Else
Me("line" & i & "1").Visible = False
Me("line" & i & "2").Visible = False
Me("line" & i & "3").Visible = False

End If
Next i
End Sub

Are I'm doing any harm by deleting it? The report works without it, but
will
it always be correct?

I had planned on using this solution, and ask the user for absolute
date
input via a read only query that would be opened via a macro in design
view
(Unique IDNumber and date only, then link that query to the crosstab).
The
solution does all what I want, but I now get this error and I don't
know
for
sure if I just can delete the offensive code. Also, it will still not
accept
the input via parameters from a form (even though the query will do fine,
it's just in the report), but this is a small price to pay if all else
work.
Any thought on this, anyone.
Thanks - I'm growing years older over this one.