Format field to Percent w/ 2 decimal places using IIF statement

[Peruanos72]

Hello all,

I'm using the following IIF statement in a query:

Avg OCA: IIf(Sum([total audited])=0,"0.00%",(1-((Sum([total pmnt
errors])+Sum([total non pmnt errors]))/Sum([total audited]))))

The statement works but it doesn't show the result in percentages. It
displays in whole numbers Ex: 1 or .987654345345 And I can't seem to change
the format to display percent with 2 decimal places like I can with other
fields where an IIF statement is NOT being used.

Thoughts??????

 

[John Spencer]

Your IIF statement is forced to return a string since you have set it to
return a string "0.00%" as one of the results. Hve it return a number value
(zero) and then you can apply number formating to the returned value.

Avg OCA: IIf(Sum([total audited])=0,0,(1-((Sum([total pmnt
errors])+Sum([total non pmnt errors]))/Sum([total audited]))))


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Peruanos72]

Thanks Both Suggestions worked beautifully.

Your IIF statement is forced to return a string since you have set it to
return a string "0.00%" as one of the results. Hve it return a number value
(zero) and then you can apply number formating to the returned value.

Avg OCA: IIf(Sum([total audited])=0,0,(1-((Sum([total pmnt
errors])+Sum([total non pmnt errors]))/Sum([total audited]))))


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hello all,

I'm using the following IIF statement in a query:

Avg OCA: IIf(Sum([total audited])=0,"0.00%",(1-((Sum([total pmnt
errors])+Sum([total non pmnt errors]))/Sum([total audited]))))

The statement works but it doesn't show the result in percentages. It
displays in whole numbers Ex: 1 or .987654345345 And I can't seem to change
the format to display percent with 2 decimal places like I can with other
fields where an IIF statement is NOT being used.

Thoughts??????

 

[Peruanos72]

Hello all,

I'm using the following IIF statement in a query:

Avg OCA: IIf(Sum([total audited])=0,"0.00%",(1-((Sum([total pmnt
errors])+Sum([total non pmnt errors]))/Sum([total audited]))))

The statement works but it doesn't show the result in percentages. It
displays in whole numbers Ex: 1 or .987654345345 And I can't seem to change
the format to display percent with 2 decimal places like I can with other
fields where an IIF statement is NOT being used.

Change the "0.00%" to 0 (zero) and surround the IIf() function with a
Format(IIf(...), "Percent").

Thanks Both Suggestions worked beautifully.