Results 1 to 13 of 13
Thread: Discount (Access 2000)

20060626, 20:45 #1
 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 ?

20060626, 21:04 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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 .

20060626, 21:29 #3
 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

20060626, 21:51 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.

20060627, 01:44 #5
 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

20060627, 01:48 #6
 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?

20060627, 04:42 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Discount (Access 2000)
You could use
Me!UnitPrice = Me!dealer * (1  Me!Skonto)

20060627, 04:47 #8
 Join Date
 Mar 2006
 Posts
 195
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Discount (Access 2000)
That suits me perfectly. Thank you !

20060628, 12:41 #9
 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 1Me!Skonto but without success.How could i avoid the wrong calculation with o ?
Thank you in advance

20060628, 12:51 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.

20060628, 15:25 #11
 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

20060628, 16:53 #12
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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>

20060628, 18:46 #13
 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