Capturing a Variable in a Report Group

[LakotaMan]

Hi all,

I've been looking through the FAQs and haven't seen one that has an answer to this. If you know of one, I apologize in advance for the repetition.

Using Version 2002, I have set up a crosstab query and am trying to create a report from it with some custom calculations. The query tracks # of incidents per month on the column headers, and rows are grouped by Dept, Incident Type (3 values) and Status (3 values). I have created some custom calcs in textboxes in one group footer and need to capture these values for use in another group footer.

I am able to get the values into variables using the Detail section's Format event. However, I can't seem to get the textboxes in the Dept. group footer to display the values of these variables. I just did the simple:

txtMarNC.Value = MarNC

in the Dept. group footer Format event, and nothing shows up.

I've put a break in my code and stepped through it to be sure the variables are getting something in them, and they are. I declared all of them in Gen Decs as Private.

Should they be Public? (I'm only using them on this one report) or am I missing something simple here?

I will appreciate any help you can give me and will explain further if I've been too cryptic,

Thanks,
Tru

 

[dhookom]

I think you have been too cryptic. What kind of calculations are you doing in your group footers that require coding? How about describing what you actually need get get done and what you are working with?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[LakotaMan]

Duane,

Sorry. . . here are the gory details:

The original crosstab query has column headers of months, the first level of row headings is Department, the second is Type of Incident, the third is Status. Each incident reported is given an ID# so I have the crosstab counting the # of IDs for the values.

I based the report on the crosstab, using groups. In the detail section I have a row of textboxes under the value row boxes. They are each named for their month: txtJanNC, txtFebNC, etc. I am using these to calculate the number of incidents per month that are not closeable, so each textbox has something like this as its control source:

=IIf([txtStatus]="N/A",[Jan])

Nobody really needs to see these textboxes but I put them there to get this number to be used later since I didn’t know how to do this in VB.

Then, I added a row in the Dept. Total footer to calculate the total number of all incidents per month, so each textbox in there has this for its control source:

=Sum([Jan])

These are named txtJanTot, txtFebTot, etc.

All this is working well, even though it’s probably very clunky (i.e. the textboxes in the detail section) but next I need to calculate the percent of closed incidents compared to the total closeable. To get the total of “closeables” I wanted to deduct the txtJanNC from the txtJanTot values. I tried using an unbound textbox in the Dept Footer, with a control source of:

=[txtJanTot]-[txtJanNC]

But that didn’t work.

Then I thought I’d use a variable to hold the NC value for the calculation in the Dept. Footer. I wanted to test this so I set up the code to put just the NC variable into the textbox, and I can’t get it to display. Here’s the code so far:

Option Compare Database
Option Explicit

Private JanNC As Variant
Private FebNC As Variant
Private MarNC As Variant
. . .etc


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
FebNC = txtFebNC.Value
End Sub

Private Sub ObserverDeptFooter_Format(Cancel As Integer, FormatCount As Integer)
totFebNC.Value = FebNC
End Sub

I am only using variant because I have some null values and get he “Invalid use of Null” message.

Obviously, my lack of VB experience shows here. Since I figure it’s probably the best way to work this out, and since I really want to get better at that aspect of it, any advice you can give will be GREATLY appreciated.

Thanks,
Tru

 

[dhookom]

I don't think you need any code. In a group footer, you should be able to use expressions like:
=Sum([Jan]) - Sum(Abs([StatusField]="N/A") * [Jan])
=Sum([Feb]) - Sum(Abs([StatusField]="N/A") * [Feb])
=Sum([Mar]) - Sum(Abs([StatusField]="N/A") * [Mar])
This method allows you to get rid of the txtJanNC etc...

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[LakotaMan]

Duane, You are definitely THE MAN!!!

I've adjusted the control sources of all my textboxes in the Dept footer, deleted the NC textboxes, and all is working like a charm.

Just one more request, if you don't mind. I've been trying to figure out the Abs function, with no success. Could you give me an idea of what it does, specifically?

Again, thanks so much!

Tru

 

[dhookom]

Abs() converts a number to an absolute value. The expression [StatusField]="N/A" returns either true/-1 or false/0. Abs() converts the value to either 1 or 0 which gets multiplied by the month field value.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[LakotaMan]

Thanks for the info, I'll put it in my notes.
And thanks for all your help!

Tru