Designing a report based on a crosstabe

[Guest]

Hello!

Hope someone can help cause this is getting into my nerves...

I have a crosstabe that I run with a parameter for "date of sales".
Date of Sale is my column and the results are showed per month. So I run the
query and set the dates from 01/jan/07 to 31/july/07 and the query returns
the 7 months I want (mm/yy). How could I design a report that that would run
with various parameters? From what I understood so far, the field in the
report must necessarily have the name of the result achieved (ex. 01/07,
02/07, 03/07...).

Hope I made myself understandable...

Cheers everyone.

 

[Arvin Meyer [MVP]]

If what you are asking is to show a column for each month regardless if
there is a value in it, In the Design View of the Crosstab, right-click on a
blank area and choose Properties. In the column header box, add ever month
separated by commas ("Jan","Feb","Mar" ... "Dec")

 

[Guest]

Hello Arvin and thanks for your quick reply.

I reckon this property.
What I need is that once I insert a parameter for exemple from March to
November, my report would start with the first column march and then stop at
november. What I'm doing is having like 20 fixed columns but I need them to
vary according to the parameter I insert.

Hope I was more clear now...

Cheers mate.

 

[Arvin Meyer [MVP]]

Here's some code from a database I wrote about 10 years ago. The code was
adapted from a KB article (I think) but I can't find it now:

Option Compare Database 'Use database order for string comparisons.
Option Explicit

' Constant for maximum number of columns XTab query would
' create plus 1 for a Totals column.
Const conTotalColumns = 13

' Variables for Database object and Recordset.
Dim dbsReport As Database
Dim rstReport As Recordset

' Variables for number of columns and row and report totals.
Dim intColumnCount As Integer
Dim lngRgColumnTotal(1 To conTotalColumns) As Long
Dim lngReportTotal As Long

Private Sub Detail1_Format(Cancel As Integer, FormatCount As Integer)
' Place values in text boxes and hide unused text boxes.

Dim intX As Integer
' Verify that not at end of recordset.
If Not rstReport.EOF Then
' If FormatCount is 1, place values from recordset into text boxes
' in detail section.
If Me.FormatCount = 1 Then
For intX = 1 To intColumnCount
' Convert Null values to 0.
Me("Col" + Format(intX)) = xtabCnulls(rstReport(intX - 1))
Next intX

' Hide unused text boxes in detail section.
For intX = intColumnCount + 2 To conTotalColumns
Me("Col" + Format(intX)).Visible = False
Next intX

' Move to next record in recordset.
rstReport.MoveNext
End If
End If


End Sub

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Guest]

Thanks a lot Arvin, but this is way out my reach...

 

[tracy]

Thanks a lot Arvin, but this is way out my reach...

 

[tracy]

Thanks a lot Arvin, but this is way out my reach...