OfficeFrustration

OfficeFrustration (http://www.officefrustration.com/index.php)
-   Setting Up & Running Reports (http://www.officefrustration.com/forumdisplay.php?f=26)
-   -   conditional numeric formatting of field output on report (http://www.officefrustration.com/showthread.php?t=626305)

JASelep February 21st, 2007 07:42 PM

conditional numeric formatting of field output on report
 
I have a report that uses one area to display either a percentage or a count

I'd like to format the report field output so that if the value is less than
1 to format the number as a 2 decimal point percent and if it is 1 or greater
format it as an integer

the conditional formatting features I see deal with the conditional display
format and not the conditional numeric formatting

I've struggled to see if I could conditionally format the data at the
ControlSource point of the field properties or at the format point but have
hit deadends on both avenues

Jim-

JASelep February 21st, 2007 07:46 PM

conditional numeric formatting of field output on report
 
Access 2003 Professional
--
Jim


"JASelep" wrote:

I have a report that uses one area to display either a percentage or a count

I'd like to format the report field output so that if the value is less than
1 to format the number as a 2 decimal point percent and if it is 1 or greater
format it as an integer

the conditional formatting features I see deal with the conditional display
format and not the conditional numeric formatting

I've struggled to see if I could conditionally format the data at the
ControlSource point of the field properties or at the format point but have
hit deadends on both avenues

Jim-


Duane Hookom February 21st, 2007 08:41 PM

conditional numeric formatting of field output on report
 
You should be able to set a control source like:
=IIf([Jim]1,Format([Jim],"0.00"),Format([Jim],"0"))
--
Duane Hookom
Microsoft Access MVP


"JASelep" wrote:

I have a report that uses one area to display either a percentage or a count

I'd like to format the report field output so that if the value is less than
1 to format the number as a 2 decimal point percent and if it is 1 or greater
format it as an integer

the conditional formatting features I see deal with the conditional display
format and not the conditional numeric formatting

I've struggled to see if I could conditionally format the data at the
ControlSource point of the field properties or at the format point but have
hit deadends on both avenues

Jim-


JASelep February 22nd, 2007 02:55 PM

conditional numeric formatting of field output on report
 
This helped, Thanks!
If I wanted the percent sign to show with 3 decimal places VS currency with
2 decimal places, how would I format that or would I use a combination of
field format and controlsource defined format functions?
--
Jim


"Duane Hookom" wrote:

You should be able to set a control source like:
=IIf([Jim]1,Format([Jim],"0.00"),Format([Jim],"0"))
--
Duane Hookom
Microsoft Access MVP


"JASelep" wrote:

I have a report that uses one area to display either a percentage or a count

I'd like to format the report field output so that if the value is less than
1 to format the number as a 2 decimal point percent and if it is 1 or greater
format it as an integer

the conditional formatting features I see deal with the conditional display
format and not the conditional numeric formatting

I've struggled to see if I could conditionally format the data at the
ControlSource point of the field properties or at the format point but have
hit deadends on both avenues

Jim-


Duane Hookom February 22nd, 2007 05:27 PM

conditional numeric formatting of field output on report
 
You can check Help for possibilities using the format function. If your
control source uses the Format() function then your Format Property is
in-effective.
--
Duane Hookom
Microsoft Access MVP


"JASelep" wrote:

This helped, Thanks!
If I wanted the percent sign to show with 3 decimal places VS currency with
2 decimal places, how would I format that or would I use a combination of
field format and controlsource defined format functions?
--
Jim


"Duane Hookom" wrote:

You should be able to set a control source like:
=IIf([Jim]1,Format([Jim],"0.00"),Format([Jim],"0"))
--
Duane Hookom
Microsoft Access MVP


"JASelep" wrote:

I have a report that uses one area to display either a percentage or a count

I'd like to format the report field output so that if the value is less than
1 to format the number as a 2 decimal point percent and if it is 1 or greater
format it as an integer

the conditional formatting features I see deal with the conditional display
format and not the conditional numeric formatting

I've struggled to see if I could conditionally format the data at the
ControlSource point of the field properties or at the format point but have
hit deadends on both avenues

Jim-


JASelep February 22nd, 2007 08:18 PM

conditional numeric formatting of field output on report
 
=IIf([TierFactor]1,[TierFactor],FormatCurrency([TierFactor],2))

works if textbox formated to format percent and decimalplaces set to 3

however formatpercent(tierfactor,3,1,1) doesn't work as described and
defined it yields an error

http://msdn.microsoft.com/library/de...afctformat.asp

however formatpercent(tierfactor,3,true,true) does
am i misinterpreting the linked information??? on formatpercent???
--
Jim


"Duane Hookom" wrote:

You can check Help for possibilities using the format function. If your
control source uses the Format() function then your Format Property is
in-effective.
--
Duane Hookom
Microsoft Access MVP


"JASelep" wrote:

This helped, Thanks!
If I wanted the percent sign to show with 3 decimal places VS currency with
2 decimal places, how would I format that or would I use a combination of
field format and controlsource defined format functions?
--
Jim


"Duane Hookom" wrote:

You should be able to set a control source like:
=IIf([Jim]1,Format([Jim],"0.00"),Format([Jim],"0"))
--
Duane Hookom
Microsoft Access MVP


"JASelep" wrote:

I have a report that uses one area to display either a percentage or a count

I'd like to format the report field output so that if the value is less than
1 to format the number as a 2 decimal point percent and if it is 1 or greater
format it as an integer

the conditional formatting features I see deal with the conditional display
format and not the conditional numeric formatting

I've struggled to see if I could conditionally format the data at the
ControlSource point of the field properties or at the format point but have
hit deadends on both avenues

Jim-


Duane Hookom February 22nd, 2007 10:34 PM

conditional numeric formatting of field output on report
 
I would have used the Format() function since I wasn't even aware of
FormatCurrency() and FormatPercent().

If you have found something that doesn't work and something that does work,
the abandon the one that doesn't and use the one that does.

--
Duane Hookom
Microsoft Access MVP


"JASelep" wrote:

=IIf([TierFactor]1,[TierFactor],FormatCurrency([TierFactor],2))

works if textbox formated to format percent and decimalplaces set to 3

however formatpercent(tierfactor,3,1,1) doesn't work as described and
defined it yields an error

http://msdn.microsoft.com/library/de...afctformat.asp

however formatpercent(tierfactor,3,true,true) does
am i misinterpreting the linked information??? on formatpercent???
--
Jim


"Duane Hookom" wrote:

You can check Help for possibilities using the format function. If your
control source uses the Format() function then your Format Property is
in-effective.
--
Duane Hookom
Microsoft Access MVP


"JASelep" wrote:

This helped, Thanks!
If I wanted the percent sign to show with 3 decimal places VS currency with
2 decimal places, how would I format that or would I use a combination of
field format and controlsource defined format functions?
--
Jim


"Duane Hookom" wrote:

You should be able to set a control source like:
=IIf([Jim]1,Format([Jim],"0.00"),Format([Jim],"0"))
--
Duane Hookom
Microsoft Access MVP


"JASelep" wrote:

I have a report that uses one area to display either a percentage or a count

I'd like to format the report field output so that if the value is less than
1 to format the number as a 2 decimal point percent and if it is 1 or greater
format it as an integer

the conditional formatting features I see deal with the conditional display
format and not the conditional numeric formatting

I've struggled to see if I could conditionally format the data at the
ControlSource point of the field properties or at the format point but have
hit deadends on both avenues

Jim-


Jeannie April 2nd, 2008 07:11 PM

conditional numeric formatting of field output on report
 
Thank you so much. Your solution worked perfectly and saved me hours of
frustration!

"Duane Hookom" wrote:

You should be able to set a control source like:
=IIf([Jim]1,Format([Jim],"0.00"),Format([Jim],"0"))
--
Duane Hookom
Microsoft Access MVP


"JASelep" wrote:

I have a report that uses one area to display either a percentage or a count

I'd like to format the report field output so that if the value is less than
1 to format the number as a 2 decimal point percent and if it is 1 or greater
format it as an integer

the conditional formatting features I see deal with the conditional display
format and not the conditional numeric formatting

I've struggled to see if I could conditionally format the data at the
ControlSource point of the field properties or at the format point but have
hit deadends on both avenues

Jim-


David McCormack[_2_] June 6th, 2010 07:52 PM

conditional numeric formatting of field output on report
 
I have tried this but I keep getting an error. This is what I have:

=IIf([Operation]=2,Format([May], "##0.0"),Format([May], "##0.0%"))

I have a field that if the object [Operation] is equals to 2, I need the
field to be in a standard number format with one decimal place. Otherwise I
need the field to be in a percentage format with one decimal place.

"Duane Hookom" wrote:

You should be able to set a control source like:
=IIf([Jim]1,Format([Jim],"0.00"),Format([Jim],"0"))
--
Duane Hookom
Microsoft Access MVP


"JASelep" wrote:

I have a report that uses one area to display either a percentage or a count

I'd like to format the report field output so that if the value is less than
1 to format the number as a 2 decimal point percent and if it is 1 or greater
format it as an integer

the conditional formatting features I see deal with the conditional display
format and not the conditional numeric formatting

I've struggled to see if I could conditionally format the data at the
ControlSource point of the field properties or at the format point but have
hit deadends on both avenues

Jim-


Marshall Barton June 6th, 2010 08:11 PM

conditional numeric formatting of field output on report
 
David McCormack wrote:

I have tried this but I keep getting an error. This is what I have:

=IIf([Operation]=2,Format([May], "##0.0"),Format([May], "##0.0%"))

I have a field that if the object [Operation] is equals to 2, I need the
field to be in a standard number format with one decimal place. Otherwise I
need the field to be in a percentage format with one decimal place.



I don't see a problem with the expression. If your error is
that the text box displays #Error, then try changing the
name of the text box to something other than the nae of a
field in the report's record source (eg. txtMay).

--
Marsh
MVP [MS Access]


All times are GMT +1. The time now is 10:33 PM.

Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
OfficeFrustration.com