Format one cell based on another

[sloshake]

In the report I have multiple rows of numbers, and sometimes this number represents revenue and sometimes its membership. I have another field on this same row that says "Revenue" or "Membership". Currently i just have it in a general format with 2 decimal places. I'd like the number field to be currency if the other field is revenue, and standard with 0 decimal if it is membership.

Is this possible in VBA or something? Thanks. This is at the lowest level too. Since I've read that the On Format only works at a higher level, i dont know if this is going to be possible.

 

[RichO]

Can you give a clearer explanation? Is this a continuous report? How many fields are there per row and what does each of those fields represent?

 

[sloshake]

Month00 Month01 Month02 Month03

Membership 132,428.00 132,428.00 132,428.00 132,428.00
Revenue 1,016,585.00 1,016,585.00 1,016,585.00 1,016,585.00

I attached an example of this too. What I did was make multiple crosstabs, then append them to each other. Month period is floating, so i needed to do a crosstab, but the information inside covers two different items, so i added in a column distinguish if its membership or revenue.

However, the units for membership and revenue is different. Membership i'd like standard, and revenue i'd like Currency.

 

[RichO]

This appears to be a continuous report, am I correct? The membership fields and revenue fields share the same textbox on the report?

If this is the case, I don't believe that you can format them in that way because what applies for one row would apply for all and the conditional formatting option only allows you to change colors and font styles.

Perhaps there is a way but I've been unable to find it.

 

[sloshake]

Yep, they do share a single text box, thus causing my difficulty in getting them to format. The membership/revenue heading share a single box on that row as well, and i was wondering if based upon that box I could do formatting.

Perhaps there's a way around this? I could create two different crosstab reports, one with membership and one with revenue, but then I dont know where to go from there...As it is, i do that, then just append one to the other.

 

[Rich]

Have you tried the Format Function?

 

[sloshake]

Nope...which one's that?

Though I found another way to do what I want. I just create two crosstabs (one revenue, one membership), then make a table off Membership with extra columns for revenue. Then make a table for revenue (since can't update off the aggregate query), and update the first table with the second.

This does require me to fix the number months is the only issue. (As far as I know)

 

[Rich]

Post a 97ver of this with some dummy data