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
"Tiago" <(E-Mail Removed)> wrote in message
news:C47C8C2B-AF02-4715-8532-(E-Mail Removed)...
> 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]" wrote:
>
>> 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")
>> --
>> Arvin Meyer, MCP, MVP
>> http://www.datastrat.com
>> http://www.mvps.org/access
>> http://www.accessmvp.com
>>
>> "Tiago" <(E-Mail Removed)> wrote in message
>> news:AE6C4548-70EC-44C6-A667-(E-Mail Removed)...
>> > 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.
>>
>>
>>
Similar Threads
