Problem with rounding code

[lccrews]

I'm using the below script to take numbers from my table and round them based on the value. If the number is above 15, I want the value to round off to one decimal place. If the number is below 15, I want the value to round off to three decimal places. The formula is working on numbers above 15, but will not place a ".0" next to whole values. I suspect that Access will not do this. Am I right? If there is a better way to do this please let me know.



SELECT Tbl_Formulas.QtyPer, Round([QtyPer],IIf([QtyPer]<=15,3,1)) AS Expr1 FROM Tbl_Formulas;

[JoeM]

The issue is because for numeric values, decimals of ".0" have no value or meaning and can be dropped.
If you change the result to Text, you can keep them, like this:

SELECT Tbl_Formulas.QtyPer, IIf([QtyPer]<=15,Format(Round([QtyPer],3),"0.000"),Format(Round([QtyPer],1),"0.0")) AS Expr1
FROM Tbl_Formulas;

[ranman256]

place this code into a module, and the query (or form field) will return your format
Ex: select Found15([field]) from table

Function Round15(ByVal pvFld)
If pvFld > 15 Then
  If (pvFld - Int(pvFld)) = 0 Then
     Round15 = Format(pvFld, "00")
  Else
     Round15 = Format(pvFld, "0.0")
  End If
Else
  Round15 = Format(pvFld, "0.000")
End If
End Function