How do I reference a calculated control (?) in a report 1

[tdfreeman]

I want to do some conditional formatting in the footer section of a report. You can see my previous question "Question Re: Working with controls on a report) to see what I am doing as it refers to the detail section of a report.

Anyway I am trying to condition the formatting on the Sum fields in the footer. When I reference the control it is not working right. I am not sure why except that maybe it is not looking at the value of the control as it shows on the report. The control source for the control is "=Sum(Field)". I am comparing the two fields in the footer section. They are the same. I say to apply the footing if they are different. It is applying the footing every time. So it is not comparing the right thing. I don't know what it is comparing.

Here is my code.

If Me![Sum of JFMIP_BEG_BAL] <> Me![Sum of AMS_BEG_BAL] Or _
Me![Sum of JFMIP_DEBIT_BAL] <> Me![Sum of AMS_DEBIT_BAL] Or _
Me![Sum of JFMIP_CREDIT_BAL] <> Me![Sum of AMS_CREDIT_BAL] Or _
Me![Sum of JFMIP_ENDING_BAL] <> Me![Sum of AMS_ENDING_BAL] Then
GroupFooter5.BackColor = RGB(204, 153, 255)
End If

Thank you in advance for your help. I have had problems referencing aggregate value controls before and have never figured it out.

Tammy Thank you for your help.

Tammy

 

[PaulBricker]

Tammy, try this.

If Sum(JFMIP_BEG_BAL]) <> Sum([AMS_BEG_BAL]) Or _
Sum([JFMIP_DEBIT_BAL]) <> Sum([AMS_DEBIT_BAL]) Or _
Sum([JFMIP_CREDIT_BAL]) <> Sum([AMS_CREDIT_BAL]) Or _
Sum(JFMIP_ENDING_BAL]) <> Sum([AMS_ENDING_BAL]) Then
GroupFooter5.BackColor = RGB(204, 153, 255)

Also, Access Reports like options so it would probably help if you had an Else clause in there. Something like
Else
GroupGooter5.BackColor = vbWhite


Paul

 

[tdfreeman]

I have tried that already and I just tried it again.

I get an error: Sub or Function not defined.

Any ideas?

Thanks,

Tammy Thank you for your help.

Tammy

 

[PaulBricker]

Where does the code stop. That usually means that Access is seeing something it can't define as a variable or thinks the value is a call to a sub or function.
Also, I missed a couple of [ in my rewrite.

Paul

 

[tdfreeman]

Caught the [ errors and fixed them. It is saying that it can't find Sum(). The report uses the Sum() function, so why can't the VBA code see it?

Thanks,

Tammy Thank you for your help.

Tammy

 

[Dalain]

Hi the problem is the field already has calulated the sum in the report. This field now contains the resault of that sum calulation. So you have to change the color based on that resault. This meen you use only need to referance the filed names

If [JFMIP_BEG_BAL] <> [AMS_BEG_BAL] Or _
[JFMIP_DEBIT_BAL] <> [AMS_DEBIT_BAL] Or _
[JFMIP_CREDIT_BAL] <> [AMS_CREDIT_BAL] Or _
JFMIP_ENDING_BAL] <> [AMS_ENDING_BAL] Then
GroupFooter5.BackColor = RGB(204, 153, 255)


Hope this helps
Pierre

 

[tdfreeman]

Thanks. I thought I had already tried that but I either misread my results or I didn't try it.

That worked.

Tammy Thank you for your help.

Tammy