Solved

Access 2010 report conditional formatting IIf statement

Posted on 2013-01-17
5
1,625 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Export to Excel 7 32
VBA to find and replace multiline text from VBA modules 8 63
Access 2010 change CurrentUser 5 32
Access join syntax when converting to T-SQL query 4 32
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

791 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