Division by Zero displayed as "N/A" 2

[bernie10]

Hello all,

Suppose I have the following query:

2005 2006 %Change
10 5 -.5
3 4 .33333333333
0 0 #Error
0 4 #Error

I want to get the table in my report to look like this:

2005 2006 %Change
10 5 -50%
3 4 33.3%
0 0 N/A
0 4 N/A

I know I could do a column in my query %Change: (IIf ([2005] = 0, "N/A", ([2006]-[2005])/[2005])) but then I do not know how to get the numerical results formatted as percentages with 1 decimal place. I can only get the following display on my report:

2005 2006 %Change
10 5 -.5
3 4 .33333333
0 0 N/A
0 4 N/A

How can I get the "N/A"'s to display and also get the numerical results from my query to display as percentages with one decimal place.

Thanks in advance for any help,
Collen

 

[Remou]

How about:

[tt]IIf([2005]>0,Format(([2006]-[2005])/[2005]*100,"0.0"),"N/A")[/tt]

 

[bernie10]

Perfect, thanks.

 

[dhookom]

I would use:
IIf([2005]=0,Null,([2006]-[2005])/[2005])
This keeps numeric columns as numbers. You can set the format property within you text box Format property to display null values as N/A.
Format: 0.00%;(0.00)%;0.00%;"N/A"

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]