Calculated Field - Rounding to .99

[huntersoasis]

So I have a Calculated Field


If the price is .01 to .08 I want it to round up to .99 if it is 1.00 to round down to .99

The current Calculated Field is this:

IIf([ORIG_LOCATION]="",0,IIf([ORIG_LOCATION]="DL",Round([SHIPPED_PRICE]*1.667,2),IIf([ORIG_LOCATION]="PJ",Round([SHIPPED_PRICE]*1.25,2),IIf([ORIG_LOCATION]="SB",Round([SHIPPED_PRICE]*1.667,2),IIf([ORIG_LOCATION]="WF",Round([SHIPPED_PRICE]*1.25,2),IIf([ORIG_LOCATION]="CNC",Round([SHIPPED_PRICE]*1.667,2),IIf([ORIG_LOCATION]="CNB",Round([SHIPPED_PRICE]*1.25,2),IIf([ORIG_LOCATION]="WFS",Round([SHIPPED_PRICE]*1.25,2),0))))))))

[huntersoasis]

The Next Calculated field needs the Same and it look like this..

IIf([ORIG_LOCATION]="",0,IIf([ORIG_LOCATION]="DL",Round([WHOLESALE_PRICE]*1.8,2),IIf([ORIG_LOCATION]="PJ",Round([WHOLESALE_PRICE]*1.3,2),IIf([ORIG_LOCATION]="SB",Round([WHOLESALE_PRICE]*1.8,2),IIf([ORIG_LOCATION]="WF",Round([WHOLESALE_PRICE]*1.35,2),IIf([ORIG_LOCATION]="CNc",Round([WHOLESALE_PRICE]*1.8,2),IIf([ORIG_LOCATION]="CNB",Round([WHOLESALE_PRICE]*1.3,2),IIf([ORIG_LOCATION]="WFS",Round([WHOLESALE_PRICE]*1.3,2),0))))))))

[ranman256]

Put this code in a module, then in your query,
replace ROUND, with ROUND99

Public Function Round99(ByVal pvCalc)
Dim cAmt As Currency
Dim cCents As Currency
Dim iDollar As Long


cAmt = Round(pvCalc, 2)
iDollar = CInt(cAmt)
cCents = cAmt - iDollar


Select Case cCents
   Case 0
      cAmt = cAmt - 0.01
   Case 0.01 To 0.08
      cAmt = iDollar + 0.99
   Case Else
      'if .09 to .98 ,do nothing
End Select


Round99 = cAmt
End Function

[huntersoasis]

ranman256,

I am unable to get this to work.

When i change the Round( to Round99 in the calculated field I get a Error

The expression
The expression
IIf([ORIG_LOCATION]="",0,IIf([ORIG_LOCATION]="DL",Round99([WHOLESALE_PRICE]*1.8,2),IIf([ORIG_LOCATION]="PJ",Round([WHOLESALE_PRICE]*1.3,2),IIf([ORIG_LOCATION]="SB",Round([WHOLESALE_PRICE]*1.8,2),IIf([ORIG_LOCATION]="WF",Round([WHOLESALE_PRICE]*1.35,2),IIf([ORIG_LOCATION]="CNc",Round([WHOLESALE_PRICE]*1.8,2),IIf([ORIG_LOCATION]="CNB",Round([WHOLESALE_PRICE]*1.3,2),IIf([ORIG_LOCATION]="WFS",Round([WHOLESALE_PRICE]*1.3,2),0))))))))
cannot be used in a calculated column

[ssanfu]

So I have a Calculated Field

Does this mean you have a table that has a calculated field or that you are doing the calculation in a control or query?

If you mean a field in a table, this is not (IMO) a good idea. I never use calculated fields in tables. If you can calculate it, then just calculate it when you need it.


See http://allenbrowne.com/casu-14.html

[huntersoasis]

This is a field in a table doing the calculated field.