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. |
|
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
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- |
#2
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|