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

  • Thread starter Thread starter Peruanos72
  • Start date Start date
P

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??????
 
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
 
Thanks Both Suggestions worked beautifully.

John Spencer said:
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??????
Click to expand...
Click to expand...
 
MGFoster said:
Peruanos72 said:
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.
Click to expand...

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

Thanks Both Suggestions worked beautifully.
 
Back
Top