conditional numeric formatting of field output on report

[Guest]

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-

 

[Guest]

Access 2003 Professional

 

[Guest]

You should be able to set a control source like:
=IIf([Jim]<1,Format([Jim],"0.00"),Format([Jim],"0"))

 

[Guest]

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

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

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-

 

[Guest]

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

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

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

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-

 

[Guest]

=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/default.asp?url=/library/en-us/vbenlr98/html/vafctformat.asp

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

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

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

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


:

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-

 

[Guest]

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

=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/default.asp?url=/library/en-us/vbenlr98/html/vafctformat.asp

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

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

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


:

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


:

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]

Thank you so much. Your solution worked perfectly and saved me hours of
frustration!

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

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]

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.

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

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]

Marshall,

Thank you for the help. It didn't help out completely, but it got me in the
right direction and I was able to get my report to work properly.

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).