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

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Add the following to your query:

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)

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
        GetPrice = dRound
    End If

End Function

Open in new window

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

thao-nhiAuthor 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?
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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)

Open in new window

Or line 4 in the function needs to be revised like so:
dRound = Round(dCost * dFactor / iRoundToNearest +.5) * iRoundToNearest

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
thao-nhiAuthor Commented:
thank you
thao-nhiAuthor Commented:
A little fine tuning please

larger numbers do not seem to like the formula:

Ex: 41 cost , price1 is 170?
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.

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.