Query Calculation

[Rhubie]

I have a query calculated field that I need to do another calculation on



Price (calculated field)
New calculation on that field -
I want to substract 4% and round to the nearest nickel.

Not sure if I can do this on a calculated field - not sure how to do the calculation in access?

Example: Round(20*(([BookPrice]-([BookPrice]*1.04))+5),0)/20

[alansidman]

In Access it is sometimes an issue trying to calculate an expression on a calculated field. To assure success, bring your calculation into a new field and add the needed calculations to it.

Example: (yourOriginalCalculation)-round(.04*(yourOriginalCalculation),roundFactor)

to round to the nearest nickel, look at this explanation by Allen Browne http://allenbrowne.com/round.html

[Rhubie]

I already have a round in the original calculation - will that be a problem? I'm confused.

BookPrice: IIf([WBCode]="03-N0051" Or [WBCode]="03-N0061",Null,Round(20*(([PackPrice]/20)+5),0)/20)

[alansidman]

What happens when you try? Does it work?

Alternatively if you want to just calculate on the BookPrice field, you could create a new query based upon the current query and run a calculation based upon the field name BookPrice. Don't know what you think of having two queries to get your answer. Sometimes, it is just easier.

[Rhubie]

I can get the formula to work - WSBookPrice: [BookPrice]-([BookPrice]*0.04), but when I try to round up to the nickel - it is not working

WS: Round(20*([BookPrice]-([BookPrice]*0.04))+5,0)/20

Not sure what I am doing wrong - I am getting 44.25 it should be 44.05

BookPrice is 45.85

got it - needed to be +.5