Create report from Crosstab query w/dates as column headings

Donna Sabol

First, I should start by saying I am creating a database to be used by
some very impatient, non-computer literate people. It needs to be
seameless in it's operation from their point of view. I want them to
do as little as possible when they run their reports.

I have a crosstab query that displays usage of items for each month.
It looks pretty much like this:

ITEM DESC UM 12/02 1/03 2/03 3/03 ...ETC.
1 Solution CS 9 5 1 3
10 Bandaids BX 50 75 25 30

This report is generated by selecting a begin date for the data
collection, in this case 12/1/02. This means that the date columns
WILL change. As the months progress they will not stay the same.

I can get the system to create a rather ugly report based on my
crosstab query, using the Tabular Report Wizard. But I don't like the
way it formats the column headings, so I want to create a report that
resides on the system and doesn't need to be created from the wizard
everytime (thus allowing me to format the page in a more pleasing
manner). Right now the margins are an inch, all the columns are too
skinny, etc.

Of course, the problem with having a report that resides in the
database and is populated by the query is that the column headings
will not change on the report, and once this month passes it will not
pull correctly...

A second issue is the table that the query pulls from has the dates
formated like this: m/yy (which is 1/03 or 12/02 etc.) But when the
crosstab query is generated it displays the dates in the column
headings as "1/1/2003". I've tried putting a formula in that displays
the date as "1/2003" but then it sees it as text and sorts the columns
wrong...if the dates were shorter, maybe letting the wizard create the
report would not be so bad...

So, how can I create a useable report for my crosstab query and/or how
can I format the dates in my crosstab query so they will still sort as
dates, not as text.

Thanks for your help everyone!

Nov 12 '05 #1

Rich P

A solution to your situation would be to create a temp table with fake
data that your crosstab query will display the way you want. For
example, the field that will serve as the column names for your crosstab
query will be a text field. Here your fake data would look like this:

12/02
1/03
2/03
3/03
...

These are would have to be strings in order for the crosstab query not
to display them as 12/1/03, etc. If the field is a data field then the
crosstab query would do the 12/1/03 thing. So the temp table would be
an intermediate table in your routine. I am guessing that your users
will invoke the report from a button on a form (at least this is the
most common way) using like DoCmd.OpenReport "Rpt1"

So if this is the case, then in the button for that you would write data
from your source data table to the temp table. For Dates, you can parse
them like this:

Dim strSql As String
DoCmd.RunSql "Delete * From tblTemp" 'initialize table
strSql = "Insert Into tblTemp(fld1, fld2, ..., fldx) Select fld1, fld2,
Month(Datefld) & '/' & Year(Datefld) As fld3, ... from tblData"
DoCmd.RunSql strSql
DoCmd.OpenReport "Rpt1"

Where Rpt1 is based on the crosstab Query. You can format your report
further using VBA code.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 12 '05 #2

DFS

"Donna Sabol" <ds********@yahoo.com> wrote in message
news:e8**************************@posting.google.c om...

First, I should start by saying I am creating a database to be used by
some very impatient, non-computer literate people. It needs to be
seameless in it's operation from their point of view. I want them to
do as little as possible when they run their reports.

I have a crosstab query that displays usage of items for each month.
It looks pretty much like this:

ITEM DESC UM 12/02 1/03 2/03 3/03 ...ETC.
1 Solution CS 9 5 1 3
10 Bandaids BX 50 75 25 30

This report is generated by selecting a begin date for the data
collection, in this case 12/1/02. This means that the date columns
WILL change. As the months progress they will not stay the same.

I can get the system to create a rather ugly report based on my
crosstab query, using the Tabular Report Wizard. But I don't like the
way it formats the column headings, so I want to create a report that
resides on the system and doesn't need to be created from the wizard
everytime (thus allowing me to format the page in a more pleasing
manner). Right now the margins are an inch, all the columns are too
skinny, etc.

Of course, the problem with having a report that resides in the
database and is populated by the query is that the column headings
will not change on the report, and once this month passes it will not
pull correctly...
Donna, you'll have to create unbound text boxes in the report's Detail
band, and bind them at runtime to the columns in the crosstab query. You'll
need to learn some VBA coding to do this.

Here's an example:

Private Sub Report_Open(Cancel As Integer)

Set db = CurrentDb()

'LINE UP CONTROLS
For i = 1 To 10
Me("txt" & i).Left = Me("lbl" & i).Left
Me("txt" & i).Width = Me("lbl" & i).Width
Me("sumTxt" & i).Left = Me("lbl" & i).Left
Me("sumTxt" & i).Width = Me("lbl" & i).Width
Next i
'SINCE THE CROSS-TAB RESULTS AREN'T KNOWN UNTIL THE QUERY IS RUN
'BIND THE CONTROLS AND SET LABELS AT RUN TIME

'FIRST BLANK OUT ALL LABELS AND TOTALS
For i = 1 To 10
Me("lbl" & i).Caption = ""
Me("sumTxt" & i).Visible = False
Next i

'BIND CONTROLS
j = 1
Set rs = db.OpenRecordset("CROSSTAB_QUERY")
If j <= 10 Then 'MAX OF 10 TEXT BOXES
For i = 3 To rs.Fields.Count - 1 '3 IS THE FIRST DATA FIELD
Me("lbl" & (j)).Caption = rs(i).Name
Me("txt" & (j)).ControlSource = rs(i).Name
Me("sumTxt" & j).Visible = True
j = j + 1
Next i
End If
rs.Close

End Sub


A second issue is the table that the query pulls from has the dates
formated like this: m/yy (which is 1/03 or 12/02 etc.) But when the
crosstab query is generated it displays the dates in the column
headings as "1/1/2003". I've tried putting a formula in that displays
the date as "1/2003" but then it sees it as text and sorts the columns
wrong...if the dates were shorter, maybe letting the wizard create the
report would not be so bad...
Try formatting them as yyyy-mm.
So, how can I create a useable report for my crosstab query and/or how
can I format the dates in my crosstab query so they will still sort as
dates, not as text.

Thanks for your help everyone!

Nov 12 '05 #3

Rich P

Incase I missed your point where you want to rename labels in your
report, here is some vba code you could use in the Report_Open event of
the Report's code module:

Private Sub Report_Open(Cancel As Integer)
Dim arr As Variant, i As Integer, RS As Recordset
arr1 = Array(lbl0, lbl1, lbl2, lbl3, lbl4, lbl5)
Set RS = CurrentDb.OpenRecordset("yourQry")
For i = 0 To 5
arr1(i).Caption = RS(i).name
Next
RS.Close
End Sub

You could actually name your Labels, A, B, C, D,... and place the label
names in the variant array above which I called arr. Then loop through
the array and recordset to get the names you want in your labels.

Also note that in a crosstab query, the first 2 or 3 columns are not the
data columns you want. So you might want to start your loop at i = 2 or
so, and set the limit to 1 - the count of all your labels because the
Recordset Object RS starts at 0.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 12 '05 #4

Donna Sabol

I've tried the code listed here by DFS, and I see the code that Rich
just posted. Here is what I currently have (with my modifications):

Private Sub Report_Open(Cancel As Integer)

Dim db As Database
Dim i As Integer
Dim j As Integer
Dim rs As Recordset

Set db = CurrentDb()

'LINE UP CONTROLS
For i = 1 To 12
Me("txt" & i).Left = Me("lbl" & i).Left
Me("txt" & i).Width = Me("lbl" & i).Width
Me("sumTxt" & i).Left = Me("lbl" & i).Left
Me("sumTxt" & i).Width = Me("lbl" & i).Width
Next i
'SINCE THE CROSS-TAB RESULTS AREN'T KNOWN UNTIL THE QUERY IS RUN
'BIND THE CONTROLS AND SET LABELS AT RUN TIME

'FIRST BLANK OUT ALL LABELS AND TOTALS
For i = 1 To 12
Me("lbl" & i).Caption = ""
Me("sumTxt" & i).Visible = False
Next i

'BIND CONTROLS
j = 1
Set rs = db.OpenRecordset("USAGE_HISTORY_Crosstab")
If j <= 12 Then 'MAX OF 12 TEXT BOXES
For i = 3 To rs.Fields.Count - 1 '3 IS THE FIRST DATA FIELD
Me("lbl" & (j)).Caption = rs(i).Name
Me("txt" & (j)).ControlSource = rs(i).Name
Me("sumTxt" & j).Visible = True
j = j + 1
Next i
End If
rs.Close

End Sub
My system doesn't seem to like the "Caption" statement though. I get
"Object doesn't support this property or method". So I REM'd out the
lines that blank out the controls and it did the same on the line that
says "...Caption = rs(i)...". Is there another function I can use other
than Caption?

Thanks for your help. I'm using Access 97 on a W2k machine.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 12 '05 #5

DFS

Answer down below:
"Donna Sabol" <ds********@yahoo.com> wrote in message
news:3f*********************@news.frii.net...

I've tried the code listed here by DFS, and I see the code that Rich
just posted. Here is what I currently have (with my modifications):

Private Sub Report_Open(Cancel As Integer)

Dim db As Database
Dim i As Integer
Dim j As Integer
Dim rs As Recordset

Set db = CurrentDb()

'LINE UP CONTROLS
For i = 1 To 12
Me("txt" & i).Left = Me("lbl" & i).Left
Me("txt" & i).Width = Me("lbl" & i).Width
Me("sumTxt" & i).Left = Me("lbl" & i).Left
Me("sumTxt" & i).Width = Me("lbl" & i).Width
Next i
'SINCE THE CROSS-TAB RESULTS AREN'T KNOWN UNTIL THE QUERY IS RUN
'BIND THE CONTROLS AND SET LABELS AT RUN TIME

'FIRST BLANK OUT ALL LABELS AND TOTALS
For i = 1 To 12
Me("lbl" & i).Caption = ""
Me("sumTxt" & i).Visible = False
Next i

'BIND CONTROLS
j = 1
Set rs = db.OpenRecordset("USAGE_HISTORY_Crosstab")
If j <= 12 Then 'MAX OF 12 TEXT BOXES
For i = 3 To rs.Fields.Count - 1 '3 IS THE FIRST DATA FIELD
Me("lbl" & (j)).Caption = rs(i).Name
Me("txt" & (j)).ControlSource = rs(i).Name
Me("sumTxt" & j).Visible = True
j = j + 1
Next i
End If
rs.Close

End Sub
My system doesn't seem to like the "Caption" statement though. I get
"Object doesn't support this property or method". So I REM'd out the
lines that blank out the controls and it did the same on the line that
says "...Caption = rs(i)...". Is there another function I can use other
than Caption?

Donna,

That code I gave you integrated with my own report, of course, which had
labels named "lbl1", "lbl2", etc. If you don't have labels with those
names, it won't work. Comment those lines out as well. But remember, you
need to not only bind the text boxes to the fields returned in the crosstab
query, but also set the corresponding label captions as well.

And looking at the code, which I edited a little before I posted it, I see
something illogical. Switch the initial If and For statements around. New
version:

j = 1
Set rs = db.OpenRecordset("USAGE_HISTORY_Crosstab")
For i = 3 To rs.Fields.Count - 1 '3 IS THE FIRST DATA FIELD
If j <= 12 Then 'MAX OF 12 TEXT BOXES
Me("lbl" & (j)).Caption = rs(i).Name
Me("txt" & (j)).ControlSource = rs(i).Name
Me("sumTxt" & j).Visible = True
j = j + 1
End If
Next i
rs.Close


Thanks for your help. I'm using Access 97 on a W2k machine.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 12 '05 #6

Rich P

If you want to loop using Me(...) you have to write it this way:

Me.Controls("lbl" & i).Caption

That should do the trick. But it is easier if you just list your labels
in the variant array because this way you are guaranteed to add the
correct name to the correct label (well, I guess Me.Controls... won't
miss either, but it is less code using the array of labels - more
efficient).

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 12 '05 #7

Reader

"Rich P" <rp*****@aol.com> wrote in message
news:3f*********************@news.frii.net...

If you want to loop using Me(...) you have to write it this way:

Me.Controls("lbl" & i).Caption
Not true, Rich. Me("lbl" & i).Caption works fine.
That should do the trick. But it is easier if you just list your labels
in the variant array because this way you are guaranteed to add the
correct name to the correct label (well, I guess Me.Controls... won't
miss either, but it is less code using the array of labels - more
efficient).

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 12 '05 #8

Rich P

Well, I have never tried

Me("lbl" & i)

always done Me.Controls(... or the array thing. But if that works,
then it is one more way to do it. If it doesn't work, then these are
just alternatives. And if it still doesn't work, then there is probably
a VBA bug somewhere in the app, maybe embedded in the property sheet of
a form control or something, or maybe there is a reference to something
(if the app was inherited from another party) that is missing.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 12 '05 #9