A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

conditional numeric formatting of field output on report



 
 
Thread Tools Display Modes
  #1  
Old February 21st, 2007, 07:42 PM posted to microsoft.public.access.reports
JASelep
external usenet poster
 
Posts: 27
Default 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-
Ads
  #2  
Old February 21st, 2007, 07:46 PM posted to microsoft.public.access.reports
JASelep
external usenet poster
 
Posts: 27
Default 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-

  #3  
Old February 21st, 2007, 08:41 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default 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-

  #4  
Old February 22nd, 2007, 02:55 PM posted to microsoft.public.access.reports
JASelep
external usenet poster
 
Posts: 27
Default 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-

  #5  
Old February 22nd, 2007, 05:27 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default 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-

  #6  
Old February 22nd, 2007, 08:18 PM posted to microsoft.public.access.reports
JASelep
external usenet poster
 
Posts: 27
Default 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-

  #7  
Old February 22nd, 2007, 10:34 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default 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-

  #8  
Old April 2nd, 2008, 07:11 PM posted to microsoft.public.access.reports
Jeannie
external usenet poster
 
Posts: 111
Default 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-

  #9  
Old June 6th, 2010, 07:52 PM posted to microsoft.public.access.reports
David McCormack[_2_]
external usenet poster
 
Posts: 4
Default 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-

  #10  
Old June 6th, 2010, 08:11 PM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default 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]
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:50 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 OfficeFrustration.
The comments are property of their posters.