Totals doubling up by using Onformat() in a crosstab report

[UnderGround]

Hi,
I am using a crosstab querry and a crosstab report as shown in the
solutions.mdb. The reports assigns vaules to unbound textboxes in the
onformat and onprint events. the totals for each column are calculated
in the footer. The unused textboxes are then hidden. The problem is
that the totals are sometimes doubling up. which is because of the way
these events are fired as i found. no i do not know any other way to do
this. its a crosstab query so i do not know the number of columns there
will be so i cant do the total in a query. i cant do a running sum of
the colums in the footer as access wont recognize the unbound column
textboxes. Is there any solution for this. Thanks in advance for any
help

 

[SA]

Try wrapping your code in the OnFormat event with this code:

If FormatCount = 1 Then
'Your code here
End if

 

[UnderGround]

I already am doing that.

 

[Duane Hookom]

I would not use the Solution.mdb method. I prefer the crosstab report method
found in the Crosstab.mdb at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4 which is faster, less
code, and much more flexible.

 

[UnderGround]

Thanks Duane,
I looked at the crosstab.mdb. In this you bound the textboxes with a
function so u know that there are 12 months. in my report the user can
select a date range and can select if he wants to see the report withen
that date range by weeks , by months or by year. i have three seperate
reports depending on what they choose to run. the difference between
the queries for the reports is that the date is formated by week (eg
yyyy/ww) by month (eg yyyy/mm) or by year (eg yyyy/yy). so in code i
assign the date vaule as headings to textboxs. and hide the unused
textboxes. and then assign the vaule the same way in the detail section
and then in the footer i sum the values that are in the details
section. so if the date range in from 1st jan to 7th jan the date
haeding will be 2006/01. but if the date is 7th to 14 jan then it will
be 2006/02 so i cant bound the textboxes like in crosstab.mdb. Is there
anything in the crosstab that i am not seeing that can be used in my
case. Am greatful for any help.
Thanks alot.

PS. Actually this is not my code. It was written by someone else i was
asked to fix it.

 

[Duane Hookom]

Assuming first you want to create a monthly interval report. I expect you
have a maximum number of columns that you can support. Let's also assume you
have a text box for the start date: Forms!frmDates!txtStart. Let's also
assume your date field is [SalesDate] (this is way too many assumptions)...

Create your column heading expression as
ColHead: "M" & DateDiff("m", Forms!frmDates!txtStart, [SalesDate])
This will create columns like "M0", "M1","M2",... where M0 is a column for
the start month.
Set your Column Headings property to: "M0", "M1","M2",..."M11"
You can set a criteria in the crosstab based on the text boxes on your form.
You need to set the query parameter types.

Bind your report to the crosstab query. You can set your column headings in
the report using text boxes with control sources like:
=DateAdd("m",0,Forms!frmDates!txtStart)
=DateAdd("m",1,Forms!frmDates!txtStart)
=DateAdd("m",2,Forms!frmDates!txtStart)
=DateAdd("m",3,Forms!frmDates!txtStart)
=DateAdd("m",4,Forms!frmDates!txtStart)
---etc---
All you need code to do is hide some column headings based on the number of
months.

You can now sum your columns/fields with no problems.

Create similar crosstabs and reports for your other date intervals.