Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Discount (Access 2000)

    Could you help me find the best way to calculate the net sum from a discount on a price ? In my case the discount is placed on the customers table, and i express is with an integer. not with percent to make the input more friendly looking. In the subofrom i express the end prices depending on the category.I show only one case, when the categoy is "dealer" , so Me!UnitPrice = Me!Dealer.
    However i want now to multiply it with the discount, if any,placed in the table Customers.so i write
    Me!UnitPrice = (Me!dealer * Me!Skonto)100 ' unit price for dealer with a discount
    But the field UnitPrice remains blank.
    Do i mistake with the expression or is he query wrong ?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Discount (Access 2000)

    The operator is for integer division, the remainder is ignored. For example 13 4 = 3 and 5 7 = 0.

    In your example, if you select the first product, dealer = 5.64 and Skonto is 10, so dealer * skonto = 50.64. Integer division by 100 results in 50.64 100 = 0. For the other products, dealer is null, so the result of the calculation will be null.

    Perhaps you should use / instead of .

  3. #3
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Discount (Access 2000)

    Thank you for your quick reply.In case of i got the result 0,56. Do you think i should give up the idea of using the integer in the discount ? My idea was that one could enter
    for each customer any discount without limitations.
    Is there in your Forum an example with a form with percentages where i could borrow some ideas ? If it is too difficult with an arbitraruy discount perhaps to use a selection of three discounts ?
    Thank you for your attention

    Regards

    peljo

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Discount (Access 2000)

    You could change the field length of the disconto field to Single, and set the Format property to Percentage. That way, the user can simply enter 7% or 10%. Access itself will take care of division by 100 in calculations.

    Access comes with a sample database, named Northwind.mdb. The Order Information table in this database contains a discounf field, and the Invoices query demonstrates how it can be used in calculations.

  5. #5
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Discount (Access 2000)

    Hans, Thank you ! I have changed accordingly the Format property and now it works excellent! I get the net result with the folloiwng:
    Me!UnitPrice = Me!dealer - Me!dealer * Me!Skonto

    I have no problems now. Thank yoy so much

  6. #6
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Discount (Access 2000)

    Hans, is there an easier way to calculate the net price as in my case, instead of substracting the unit price and the sum with the discount?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Discount (Access 2000)

    You could use

    Me!UnitPrice = Me!dealer * (1 - Me!Skonto)

  8. #8
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Discount (Access 2000)

    That suits me perfectly. Thank you !

  9. #9
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Discount (Access 2000)

    Dear Hans,
    I have the following problem :
    When i enter some percentage for the SKonto then my code works excellnt:
    Me!UnitPrice = Me!dealer * (1 - Me!Skonto) ' unit price for dealer
    However, when the customer has no percentage, i.e. the perentage is 0, , then th unit price shows no figures at all.I am sure the reason lies with the 0 in the calculations.My default for the field Skonto in the table is 0.I tried to use NZ before the 1-Me!Skonto but without success.How could i avoid the wrong calculation with o ?
    Thank you in advance

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Discount (Access 2000)

    Are you sure the problem lies in Skonto? If it is 0, the calculation should work. However, in the database you attached to the first post in this thread, there is no value for dealer for the second and third product. If dealer is blank, the unit price will be blank too.

  11. #11
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Discount (Access 2000)

    yes, it comes with the availability of the Skonto as could be seen in the attachement.The price of the first product is 5,64.However the first customer, called here aaa, has no Skonto.Therefore if you choose the first customer aaa from the combo box,and then choose the product, no unit price appears.Yet if you enter a Skonto for the first customer,then the unit price appears.And i want in the case of the customer aaa to have a unit price of 5,64, i.e. without any Skonto
    Thank you in advance for your help

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Discount (Access 2000)

    You originally stated that the Skonto was 0, now it turns out to be null (blank). You can use the following code to use 0 if Skonto is null:
    <code>
    Me!UnitPrice = Me!dealer * (1 - Nz(Me!Skonto, 0))</code>

  13. #13
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Discount (Access 2000)

    Thank you so much, Hans! yes,now the unit price appears. Obviously i have given to you the wrong information about the Null, what i had in mind was that i have made the default value to be 0, but 0 is not blank.

    Peljo

Posting Permissions

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