# Thread: calculated fields (MS Access 2007)

1. ## calculated fields (MS Access 2007)

In a form to manage articles i need two calculated fields. The first one needs to calculate in percent the difference between the selling price and the purchaseprice. The second calculated field must calculate the inverse in percent.
In the property window for the first field i typed the following code:
= ((sellingprice - purchaseprice) / (purchaseprice/100))/100 .The last division by 100 seems to be needed to get the wright result,otherwise we get a result that is 100 times greater. So it seems that telling Access that the notation has to be in percent, doesn't work the same as is the case by telling the same for a field in the designview of a table.
This code seems to work well, except when an article is encountered that by mean of promotion is given away for nothing. In such a case the possibility exists that the purchaseprice as well as the sellingprice equals zero. Because dividing by zero is not allowed , what is shown as result in this field on the form is a kind of text preceeded by # . In order to solve this problem i changed the code as follows:
= NZ(((sellingprice - purchaseprice)/ (purcaseprice/100))/100),0) but this doesn't seems to solve the problem. I hoped in such a case to get zero as result . What did I do wrong ?

2. ## Re: calculated fields (MS Access 2007)

You would use the nz function to test if the field has a value or is null. You need the iif function. IIF([purchaseprice] = 0, 0,([sellingprice]-[purchaseprice])/[purchaseprice])

3. ## Re: calculated fields (MS Access 2007)

Thank you very much for the help.
I wish you all the best for 2009 and a lot of fun with programming
Jacques

4. ## Re: calculated fields (MS Access 2007)

When i type in the code as you proposed in your recent reply : IIf([purchaseprice] = 0,0,([sellingprice] - [purchaseprice])/[purchaseprice]) , save the changing, close the form and reopen the form, the code is again changed to ([sellingprice] - [purchaseprice])/[purchaseprice] . I can't get it fixed that the IIf clause remains saved. The rest of the formular that remains works fine, but because the IIf function isn't there , the problem i mentioned when the sellingprice is 0 remains. Why isn't it possible to extend the formular with the IIf clause ? PS. I type the code in the propertysheet of the textboxes.

5. ## Re: calculated fields (MS Access 2007)

Formulas should always begin with =, so you should use
<code>
=IIf([purchaseprice]=0,0,([sellingprice]-[purchaseprice])/[purchaseprice])</code>

6. ## Re: calculated fields (MS Access 2007)

In my last answer i forgot to type the "=", but i can assure you that my code i tpyped in the propertysheet was indeed exact the same as the one you proposed in the last reply . Is it possible that a gap between the sign "= " and the "iif" can be the reason that something don't work ? I'l give it a try.

Thanks for the help

7. ## Re: calculated fields (MS Access 2007)

There should *not* be a space between the = and the rest of the formula.

8. ## Re: calculated fields (MS Access 2007)

Thank you

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•