# Calculated field in Access 2010

What I have:
A  table with the fields as described below:
Product ID, Description, Cost, Price1, Price2

What I want to do:
1. I want to create a calculated field in a query that will meet the following criteria:
Price1= cost * 4.2
Everything is rounded to the nearest \$5.
If the result is less than \$5, the minimum charge is \$5.
If the product id starts with  "X" (it could be X3000-S090-B or whatever the case might be),  the minimum charge is \$25.

Price2 field has the same requirements, just a different markup like 5.6

2. Make a new table with the results above in a separate query

thanks
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:

Price1: IIf(Round([COST]*4.2/5)*5<IIf([ProductID] Like "X*",25,5),IIf([ProductID] Like "X*",25,5),Round([COST]*4.2/5)*5)

Price2: IIf(Round([COST]*5.6/5)*5<IIf([ProductID] Like "X*",25,5),IIf([ProductID] Like "X*",25,5),Round([COST]*5.6/5)*5)

Ron
Commented:
Another option is with the use of a custom function like so:
``````Public Function GetPrice(dCost As Double, dFactor As Double, sProductID As String, iRoundToNearest As Integer) As Double
Dim dRound As Double

dRound = Round(dCost * dFactor / iRoundToNearest) * iRoundToNearest
mincharge = IIf(sProductID Like "X*", 25, 5)

If dRound < mincharge Then
GetPrice = mincharge
Else
GetPrice = dRound
End If

End Function
``````
Then you can just use these in your query:
Price1: GetPrice([cost],4.2,[ProductID],5)
Price2: GetPrice([cost],5.6,[ProductID],5)

Ron
Author Commented:
Price1: IIf(Round([COST]*4.2/5)*5<IIf([ProductID] Like "X*",25,5),IIf([ProductID] Like "X*",25,5),Round([COST]*4.2/5)*5)

The round up formula doesn't seem to work. In many cases, it actually rounds down
ex: When cost = 4, the query with the above formula gives Price1 = 15 whereas the ceiling function in Excel: Ceiling (x,5) gives the correct results = 20

4      4*4.2 =16.8      20

how to fix this?
Commented:
You didn't say anything about rounding up.  All you said was "rounded to the nearest \$5"

To round up, you need to add .5 to the equation like so:
``````Price1: IIf(Round([COST]*4.2/5+.5)*5<IIf([ProductID] Like "X*",25,5),IIf([ProductID] Like "X*",25,5),Round([COST]*4.2/5+.5)*5)
``````
Or line 4 in the function needs to be revised like so:
``````dRound = Round(dCost * dFactor / iRoundToNearest +.5) * iRoundToNearest
``````

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
thank you
Author Commented:

larger numbers do not seem to like the formula:

Ex: 41 cost , price1 is 170?
Commented:
It works fine on mine.  I'm assuming you're using the formula and not the function.  There are 2 places there that you needed to add .5 to.  Just copy and paste the formula I gave you.

Ron
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.