Calculation

[Bob Fitz]

Maybe:

Round((100-DLookUp("[Discount]","[Promo_code]","[code]= '" & [Promo] & "'"))*[Total])

[Erictsang]

Bob

With your formular, i get wrong calculation

If the [total] is 60 and the discount rate is 5%

it should be (1-5%)* 60=57


Discounted Fee: Round(100-(DLookUp("[Discount]","[Promo_code]","[code]= '" & [Promo] & "'"))*[Total]) but the outcome is 97


Any idea why?
Eric

[Bob Fitz]

I don't understand why you have used Round(). I have tested following Calculation which works for me.

Discounted Fee:((100-DLookUp("[Discount]","[Promo_code]","[code]= '" & [Promo] & "'"))*[Total])/100

[Bob Fitz]

Sorry, my mistake. I understand now why you have used Round() so calculation should be:

Discounted Fee:Round(((100-DLookUp("[Discount]","[Promo_code]","[code]= '" & [Promo] & "'"))*[Total])/100,2)

[Erictsang]

Bob

I have made the following to solve the problem but when i come to round up, it wrong again.

Discount : Round(1-DLookUp("[Discount]","[Promo_code]","[code]= '" & [Promo] & "'")*[Total],1)

the syntaxof round should be

Round (expression, decimal)

[Bob Fitz]

Have you tried the expression I gave in post #19

[Erictsang]

Thanks, i applied your expression but the result is wrong
B100 , is 5%, so for $60-5% , that is 57



Something wrong

[Erictsang]

By the way, how i can upload the access file on this forum?!

[Bob Fitz]

By the way, how i can upload the access file on this forum?!

To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.

[Erictsang]

Bob

it is not working


I have saved the file in Dropbox, appreciate if you can take a look

Million thanks


https://www.dropbox.com/s/9zhh6a9sw5...se6.accdb?dl=0

[Bob Fitz]

Take a look at the attached file

[Erictsang]

Bob

Thanks and it is solved

But how can i save the result into the master record ?

[Bob Fitz]

It is not usually considered to be good practice to save calculations, rather do the calculation whenever and wherever it is needed. See: http://allenbrowne.com/casu-14.html

That said, if this were one of those times when it was considered to be better to save the calculated results to a table you could do it with code in the forms AfterUpdate event as shown in the attached file:

[Erictsang]

BOB

Thanks so much for your help

I have another issue on the Requery.

I have no idea why i place the me.requery at the Choice form. Which trigger the choice query and displayed in the Choice subform.

it does not be able to refresh, i have to click refresh everytime.

Do you mind to have a look for me

https://www.dropbox.com/s/cb2sgihf6a...est.accdb?dl=0

Thanks thanks and thanks

Eric

[Bob Fitz]

I wouldn't use a query to present data to the user. Better to use a report or a form. Perhaps the attached db would suffice.