Need a Cross Tab Report Fix

[DeCiDeR]

Here is the current report which I run.

Grouped by Region
Facility
Month
Days (Cumulative Total)

Here is how it shows:

1 2 3 4 5 6 7 8
............. 31

MI
U of M June 2 5 8 9 9 12 13
15.............40
May 1 2 2 7 10 10 16
20.............50

Above table it goes from 1....31 days. First it groups by Region, then
it has Facility and within Facility it shows Months (Currently, the
report ask for StartDate, EndDate) Here the current month is June and
Previous Month is May. Within the month it is doing the cumulative
Total.

What I need is allmost same but like below.

1 2 3 4 5 6 7 8
............ 31

MI
U of M June 2 5 8 9 9 12 13
15.............40
May 50

What I need in the report is to show Cumulative total by day for
current month and just the total for previous month.

How can I do this?

Please help
Thanks
Minesh

 

[Duane Hookom]

Do you enter a start date of May 1 and an end date of June 31? Assuming you
have two months displaying as separate records in descending order, you
could use code in the On Format event of the Detail Section to set the "day"
text boxes to invisible if the month displayed is not the same as the month
of the End Date.

 

[DeCiDeR]

I enter the Date of June 1 to June 30... My Crosstab queries will then
pull date for previous month too and the date selected. When my reports
run, it will do selected month and previous month. How to hide the days
of just the previous month and do the total only.. i still need by day
for current month.

 

[Duane Hookom]

As per my original reply:
"you could use code in the On Format event of the Detail Section to set the
"day"
text boxes to invisible if the month displayed is not the same as the month
of the End Date"

 

[DeCiDeR]

What code do I have to write.. I will need help in writing the code..
please tell me if you need field name, query name, anything which might
be useful to write the code..

The query where it is pulling data is lets say CrossTab Query
Date Field name is: EventDate

Form name where it ask for dates is EntryForm
Form as StartDate and EndDate which user enters

Report Name is CrossTab Report

would you be able to create a code out of this..

Thanks

 

[Duane Hookom]

I would first select all the controls in the detail section that I might
want to hide. View the Tag property to set them all to HideMe.
Then, use code in the On Format event of the section like:

Dim ctl as Control
For Each ctl in Me.Section(0).Controls
If ctl.Tag = "HideMe" Then
ctl.Visible = Month(Me.txtDateField0 =
Month(Forms!EntryForm!EndDate)
End If
Next

This assumes you have a field named txtDateField in your detail section that
has the date for the month.