Hey everyone,

I have a report that shows various Accounts (Field name is "Account Name") as well as their corresponding values (Field name is "Q1"). Within the Q1 field, there are numbers (Standard format needed) and as percentage (% format needed). Is there any way for the format of Q1 to be dependent on the value in "Account Name" ?

For example, if Account Name = "Margin", then format Q1 as %, else format it as Standard. All of the values are calculated outside of Access, therefore they are all contained within one field (Q1).

I need the report to look like this:


Thank you for any and all help!

jonman03,



For something like this, I think I'd create some VBA code in the OnFormat event for the section where the Q1 textbox appears.

Something along the lines of:



Hope this helps,

Dennis

Hi jonman03,



I don't have 2010 in front of me right now, but I wonder if something like that is now possible with the new version of Access using Conditional Formatting because although what Dennis posted usually works in earlier versions of Access, it is not going to work if you are viewing the report in Report View.

Just my 2 cents...

Actually, you could do this in a textbox like this (without any VBA):

=IIf([Account Name]="Margin",Format([Q1],"0.0%"),Format([Q1],"#,##0"))

HTH
Joe

Dennis,

Thank you for the quick reply. I have used and modified the code, and it does appear to be working, however, I can not view the "dependent" formatting in Report view (Print preview only), like theDBguy mentioned... Is there any work around to this? The standard UI Conditional formatting menu in '10 has not changed, you can only specify B/U/I and shading. However, may there be more options if you do conditional formatting via VBA? I am not sure.. maybe you could offer some insight on that as well?




Thank you Joe, this also works, but is going to require me to create a duplicate text box. If I enter this formula as the control source for the Q1 text box, it indicates a circular reference. I would probably have to hide the original Q1 box, then create a duplicate that does this 'dependent' formatting.. Or am I missing something basic?

Thanks for the help everyone.

You don't need to put this as the control source for the Q1 field/control. Delete the control for the Q1 field (in the report) and add another textbox and just enter the formula. Place this on the report where you want the Q1.

See attached.

HTH
Joe

Joe,

Thank you very much. I am using this and it is working great. However, I'm running into an issue when trying to find the difference between 2 of these custom formatted text boxes. I've expanded my report to show Q1 and Q2 (both formatted using the formula and process you've outlined above), I then have a 3rd text box named "Variance" that calculates Q1 - Q2. This works correctly for all of the accounts (those formatted as a number) except the box formatted as a percentage. When trying to subtract these Access is giving me the #Type! error. Any idea what could be causing this? The number formats I have specified are as follows (For both Q1 and Q2 boxes):


Thank you for the help!

jonman03,



It's probably simpler to do the calculation within the query driving the report, and just bind a textbox to that calculated value.

Hope this helps,

Dennis

As Dennis very correctly points out, it is probably simpler to just do the calculations in the query. However, that said, you could use this (below) in the report in the "Variance" column, using the same approach as with the Q1 and Q2 columns:



(tested)

Remember, of course, how to interpret the percentage difference.... It is just a difference between two percentage figures not a percentage difference between the figures (as I am sure you already know - just thought I would add that).

HTH
Joe