Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Brugge, Belgium
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    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. #3
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Brugge, Belgium
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Brugge, Belgium
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #6
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Brugge, Belgium
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: calculated fields (MS Access 2007)

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

  8. #8
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Brugge, Belgium
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •