Solved

Access 2010 report conditional formatting IIf statement

Posted on 2013-01-17
5
1,632 Views
Last Modified: 2013-01-22
Hi just trying to apply conditional formatting to a certain section of my report.

I need to differentiate one set of values as a number rather than a percent. All are set as percent right now, but if the domain equals a certain value, I need it to format the fields in the section as a number (not a percent) and for all else, they need to stay percents.

IIf([domain]="auth_dispo_amtpmpm",FormatNumber([panel_%],[,2][,1][,1]),FormatPercent([panel_%[,2][,1][,1])

IIf([domain]="auth_dispo_amtpmpm",Format([panel_%], “Standard”),Format([panel_%], “Percent”))


Those examples have been what I am trying to do, but it won't work, please help.
0
Comment
Question by:IEHP1
  • 3
  • 2
5 Comments
 

Author Comment

by:IEHP1
ID: 38789321
Oh, I got it working in the query instead of conditional formatting in the report (don't know why it didn't work in the report?)??

but I had to remove UCase([my table].[same field name as the field name used in IIf statement]).

I don't know why I had to remove it (was giving me an error message saying "You tried to execute ~~~~~~~~~~~~~~~~ not as part of an aggregate function" ?   Both of the fields were using Group By??

So I tried to use conditional formatting in the report to make the first letter of the field I want capitalized, but won't work?

Please let me know why? This is frustrating me?
0
 

Author Comment

by:IEHP1
ID: 38789864
If I provide a screenshot of the error message, would that help?
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 38792659
I would not do this in the report itself, but in the query, something like:

SELECT Field1, Format([Field2], iif([Field1] = 2, "0.0", "0.0%"))

However, using the Format function will cause the output to become text, so you will need to right align the output in your textbox.
0
 

Author Comment

by:IEHP1
ID: 38806058
yes, thank you for responding fyed,

I can get the IIf statement in the query for each of the pct columns, but I guess since the field called [domain] is in each of the IIf statements, my UCase function that was originally in the [domain] field gives an error message. So I had to take it out.

Is there any way I can get the [domain] field to be capitalized as well (the IIf statements work correctly in the query as of now).
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38808276
Can you post the entire SQL and identify where the UCASE function call was that is now giving an error message?
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question