473,544 Members | 1,822 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Create report from Crosstab query w/dates as column headings

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
8 7554
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.OpenRepor t "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.OpenRepor t "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********@yah oo.com> wrote in message
news:e8******** *************** ***@posting.goo gle.com...
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(Can cel 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.OpenRecordse t("CROSSTAB_QUE RY")
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)).ControlSou rce = 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
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(Can cel As Integer)
Dim arr As Variant, i As Integer, RS As Recordset
arr1 = Array(lbl0, lbl1, lbl2, lbl3, lbl4, lbl5)
Set RS = CurrentDb.OpenR ecordset("yourQ ry")
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
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(Can cel 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.OpenRecordse t("USAGE_HISTOR Y_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)).ControlSou rce = 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********@yah oo.com> wrote in message
news:3f******** *************@n ews.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(Can cel 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.OpenRecordse t("USAGE_HISTOR Y_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)).ControlSou rce = 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.OpenRecordse t("USAGE_HISTOR Y_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)).ControlSou rce = 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
If you want to loop using Me(...) you have to write it this way:

Me.Controls("lb l" & 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

"Rich P" <rp*****@aol.co m> wrote in message
news:3f******** *************@n ews.frii.net...
If you want to loop using Me(...) you have to write it this way:

Me.Controls("lb l" & 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
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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
1671
by: ITMA | last post by:
Any Google search will show that somewhere out there is a kind spirited Michel Walsh has been most generous in his time over the years in giving, in reply to several postings on the matter, a Function as a solution to holding column headings constant, so that a cross tab query (the fields of which would otherwise change depending on the...
10
1648
by: pw | last post by:
Hi, Is that possible if the crosstab query is dynamic? Doesn't seem so as I have to specify the control source for the text boxes and the number of columns may change, along with their field names. Just thought I'd take a shot in the dark. -pw
4
2082
by: No Spam | last post by:
Dear Access 2000 users, I have a crosstab query that puts together certain information perfectly. It has a criteria that is based on a form that limits how many columns are returned based on the date selected. Here's the problem: The report that the crosstab query feeds was initially created to show all the dates and thus, all 26...
3
3094
by: KevLow | last post by:
Hi, Hope some kind soul can help me out here.. I'm trying to programmatically modify the column headings of a crosstab query such that it can be dynamic based on user specified period (Month Year to Month Year) So far i have tried to use the following code: //
2
2912
by: Keith Wilby | last post by:
I have a report that is based upon a crosstab query which return only the columns (fields) it has data for. When my report runs it sometimes fails because some of the text boxes don't have a field in the query. I've written some code to "fake" the missing field names which works perfectly but is quite complex and I'm wondering if there's a...
0
2305
by: Peter Herath | last post by:
I want to create a custormizable report . For an example, there's a form with four combo boxes and two of them having database tables columns/field names as values in the combo box(one for select row filed and other one for select column filed in the report) when u select items in that combo boxes and press a Button then selected items should go...
1
2636
by: c8tz | last post by:
This is a query I have created that picks up the top 3 dates for this data (for example) : Tree Pos2 Date 1 15 23/08/2005 1 20 12/02/2006 1 32 15/10/2006 2 17 23/08/2005 2 18 12/02/2006
1
1790
by: juls | last post by:
Hi experts: I want to seek an advice from all Access experts to give me an idea or sample program in vba or how to make crosstab query that can be printed in the report of which the outfield may vary depending on the number of transactions. Putting a permanent heading/value in the column headings of crosstab query is not good enough when the...
8
5899
by: Paul H | last post by:
I want to base a form on a crosstab query. The query shows statistics for a user defined period. The column headings will look something like this: ClientID Month01 Month02 Month03 etc.. So if the user selects 3 months in the criteria form, there will be 3 (month) column headings, if he selects 6 months, the will be 6 headings.
0
7437
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7373
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7625
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7389
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7717
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
5928
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
4930
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
1
1848
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
677
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.