need to add an "If" in existing expression ?

[isildad]

I have a table with three columns.
Column one is my customer's name
Colum two is the yearly sales to this customer for 2003
Column three is the yearly sales to this customer for 2004

I use a form that adds / edits the records in the table.
I have added a text box to the form and called it +/- %
and gave it this expression =([2004]-[2003])/[2003]
This works and the results are :

example 1 : 2003 = $100 ..... 2004 = $200 ..... +/-% = 100%
example 2 : 2003 = $100 ..... 2004 = $50 ....... +/-% = -50%

I need to add to this a condition. That is in situations where there is no value in "2003"(i.e. new customer in 2004)

As is at present if the field "2003" is empty and the field "2004" has a value ... after the update, the field +/- % stays empty and that's OK .

I would rather see in +/- % "N/A New Account"
How can I incorporate this "If" in the formula ?

Also in example 1 the value returned is 100% ....
How can I make it show as +100% ?

 

[Andrew Fergus]

Hi isildad

Change the format in the text box to this :

+0.00%;-0.00%[Red]
{or +0.0%;-0.0%[Red] or +0%;-0%[Red] depending on how many decimal places you want}

You will need to manually type this in. And with this format you don't need the conditional format that you typed in earlier.

And to show "N/A New Account" if [2003] = 0, then change your formula to this :

=IIf( Nz([2003]) =0, "N/A New Account" , ([2004]-[2003])/[2003] )

HTH, Andrew

 

[isildad]

Thank you very much Andrew.
Your solutions took care of both problems.