Replies

Oh Hans I know that but my database is very complicated and I must include all the conditions in it.Apart form that I didn

Could you help me modify the database ? I need to make the folowing modifications:

1. The whole discounting procedure should refer only when kindid from table tkind is 13, denoted by “extra”.Therefore to each cliend a category is assigned, in my case it ia client aaa, who has tkind = extra.
2. When case is 13, the Unit price should be = office :
Case 13
Me!UnitPrice = Me!office
3. In the AFter update event of the cartons, i want to make the field Me!Unit price = Me!NewUnitPrice. In this way it will be easier to make the further calculations
I tried many times but i cannot do it. Will you help ?

kind regards

Solar

Thank you Hans, for everything. I am deeply grateful.I will work on it the next days but i want to express my gratitude now.Thank you

Thank you Hans !
In the underlying query i use now the DLookup function to find the price.It is OK, i just cannot calculate the extended price.The line
Me!extendedprice = (Me!Quantity * Me!NewUnitPrice) just does not work.
My problems will be solved if I succeed somehow to obtain the extended price so as to be seen on the list box on the screen.I think it is not logical to be in the before update because Access does not know how many liters will be bought and hence cannot calculate the discount. Therefore, if I write Me!extendedprice = (Me!Quantity * Me!UnitPrice), the extended price will be obtained. Jist to make myslef clear. Unitprice is the usual, convention al price and the NewUnitPrice will be the price calculated with the percentage discount calculated as per the previous thread.Can you help ?

Thank you for your reply. Sorry I cannot express myself clearly.Anyway what I want is to calculate the field

Dear Hans

i want to thank very much indeed , this is a great solution. The problems i am facing are of a quite different kind and i will send another thread for that.

Yes, i simply cut this field out since there are 10 such fields and i wanted to slim down the table in order to send it easier. The table is called Products

Dear Hans

Thank you so much for your attachement.It really works fine in the stripped down database.I have another problem.When i stipped down the database, i didnt enter a code in the BeforeUpdate event of the form which is :
Me!branch0 = Me!branch0 – Me.cartons
This codes gives rise to an error saying :

The expression you entered has a field,control or property name that Microsoft Access cant find

This happened when i attached the lookup field to the query1,as the control source of the form :
NewUnitPrice: CCur(Nz(DLookUp(“NewUnitPrice”;”qryAmount”;”OrderID=” & [Orders].[OrderID]);0))

Could i look up the field from the form itself or it is the same ?

Dear Hans

i am trying to implement the wonderful solution into my database. The subform of my dataabse is caled FOrder details extended.its record source is Query1. To this Query1 i tried to add on the query QueryAmount and to add its field NewUnitprice into the Query1.However i get the message “you cant go tothe specified record”.How can i avoid this problem ?

I made a quick check and found out that it is exactly spot on,exactly what i want. i cannot stop wondering how a formidable task can be solved is such a way.It is awsome, this solutiion..Thank so much indeed showing to all how powerful Access is

Thank you for you very interesting reply. I could use this powerful query and would like to ask you the following. How can I see the discounts received for each order, since the query gives only the total discount ? For example for the client aa I have :
CompanyName M TotalLiters Discount reseller unitprice orderid
aaa 2007 05 205 5,00% 30,00

Thank you !

yes,of course ! it shoulld be currency ! Thank you !

Thank you Groveli !

Thank you, the code gives now the correct figures