Is there a way to make positive numbers appear as +XX ?

[MC]

Hi,

I have a query with two calculated fields with the following formulas:

Usage Change over Quarter: nz([Current Quarter Usage to
date],0)-nz([Previous Quarter Usage],0)

Percent Change in Usage over Quarter: IIf([Previous Quarter
Usage]=0,IIf([Current Quarter Usage to date]=0,0,Null),(nz([Current Quarter
Usage to date],0)-nz([Previous Quarter Usage],0))/nz([Previous Quarter
Usage],0))

These are working fine; my question is this: Can I make it so that, for
example if Usage increased by 10, the formula returns "+10" rather than "10"?
Same goes for the percent change (can it display "+10%" rather than just
"10%"?).

Thanks for any help,
MC

 

[Bob Barrows [MVP]]

Hi,

I have a query with two calculated fields with the following formulas:

Usage Change over Quarter: nz([Current Quarter Usage to
date],0)-nz([Previous Quarter Usage],0)

Percent Change in Usage over Quarter: IIf([Previous Quarter
Usage]=0,IIf([Current Quarter Usage to date]=0,0,Null),(nz([Current
Quarter Usage to date],0)-nz([Previous Quarter
Usage],0))/nz([Previous Quarter Usage],0))

These are working fine; my question is this: Can I make it so that,
for example if Usage increased by 10, the formula returns "+10"
rather than "10"? Same goes for the percent change (can it display
"+10%" rather than just "10%"?).

Sure: wrap it all in another iif which returns the strings you want. to
make it more manageable, you should save the query in which that formula
appears (change the alias to avoid the circular reference problem, how
about NumericPercentChange) and use that query as the souce for a new
query:

Percent Change in Usage over Quarter: iif(
NumericPercentChange>=0
,"+" & NumericPercentChange & "%"
,"-" & NumericPercentChange & "%")

 

[John Spencer]

You can use the format property or the format function to display the
plus sign
The format to use would probably look like the following.

+#,###;-#,###;0;

In a query, you should be able to use the following which will return a
string value.

Format(nz([Current Quarter Usage to date],0)-nz([Previous Quarter
Usage],0),"+#,###;-#,###;0")


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

 

[Bob Barrows [MVP]]

You can use the format property or the format function to display the
plus sign

Darn! I've been doing ASP so long that I've gotten out of the habit of
grabbing the format() function out of the toolkit!

 

[MC]

Thanks, that worked perfectly!

You can use the format property or the format function to display the
plus sign
The format to use would probably look like the following.

+#,###;-#,###;0;

In a query, you should be able to use the following which will return a
string value.

Format(nz([Current Quarter Usage to date],0)-nz([Previous Quarter
Usage],0),"+#,###;-#,###;0")


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

Hi,

I have a query with two calculated fields with the following formulas:

Usage Change over Quarter: nz([Current Quarter Usage to
date],0)-nz([Previous Quarter Usage],0)

Percent Change in Usage over Quarter: IIf([Previous Quarter
Usage]=0,IIf([Current Quarter Usage to date]=0,0,Null),(nz([Current Quarter
Usage to date],0)-nz([Previous Quarter Usage],0))/nz([Previous Quarter
Usage],0))

These are working fine; my question is this: Can I make it so that, for
example if Usage increased by 10, the formula returns "+10" rather than "10"?
Same goes for the percent change (can it display "+10%" rather than just
"10%"?).

Thanks for any help,
MC