UPDATE prodfeatures2 SET featureprice = featureprice * 0.6316;
I am trying to setup a round of to the nearest hundredth. HELP!
I do not want the answer to be 104.7648, I would want it to be 104.76.
I do not want the answer to be 104.7668, I would want it to be 104.77.
UPDATE prodfeatures2 SET featureprice = ROUND(featureprice * 0.6316,2)
I don't think you have provided enough data to deduce which rounding algorithm you require.
If your spec tells you which rounding algorithm to use then please post it.
If your spec fails to tell you which rounding algorithm then raise the issue with the designer.
Generally speaking, SQL isn't designed for math calculations. Consider doing rounding in another tier. If you do, be sure to store values using DECIMAL with an additional decimal place than required in the front end.
Please see: http://stackoverflow.com/questions/137114/how-to-round-in-ms-access-vba
To quote an excerpt:
"The Round function performs round to even, which is different from round to larger." --Microsoft
Format always rounds up.
Debug.Print Round(19.955, 2)
'Answer: 19.95
Debug.Print Format(19.955, "#.00")
'Answer: 19.96
In this case:
UPDATE prodfeatures2 SET featureprice = CCUR(Format(featureprice * 0.6316,'#.00'))
Here's my MS-Access specific answer: there's something fishy about the question!
Unless featureprice is an extremely large or extremely large small amount, and the metadata suggests that it is not, multiplying by a decimal literal such as 0.6316 will coerse the result to type DECIMAL.Now, by nature, the DECIMAL type in Access (ACE, Jet, whatever), rounds by truncation e.g. if you could do this:
SELECT CAST(104.7668 AS DECIMAL(17, 2)
it would round to 104.76... of course you can't do this in Access because it doesn't support the SQL Standard syntax and its own proprietary syntax CDEC() was broken from day one and still hasn't been fixed in ACE (rolls eyes). But what you can do is this:
CREATE TABLE TestDecimal
(
dec_col DECIMAL(17, 2) NOT NULL UNIQUE
);
INSERT INTO TestDecimal (dec_col)
VALUES (104.7668);
SELECT dec_col FROM TestDecimal;
-- 104.76 -- truncated
I'm going to guess that your prodfeatures2 column is type CURRENCY and I suggest that if don't want your result to be cast as a DECIMAL, and what we can tell from your algorithm you do not, then your SQL is missing a cast.
Further, you want the result to be two decimal places, yet the original values are not to two decimal places. For example:
SELECT CCUR(CCUR(165.87) * 0.6316)
-- 104.7635 -- too low
SELECT CCUR(CCUR(165.88) * 0.6316)
-- 104.7698 -- too high
SELECT CCUR(CCUR(165.872) * 0.6316)
-- 104.7648 -- spot on
So the values are failing to be rounded to two dp by an earlier process but needs to be two dp after this process? As I say, something may smell here and you have bugs you haven't yet tracked down... or there's more to this than you are revealing here.
What is the basis for your assertion that multipying by a decimal coerces the result to a decimal data type?
(Tongue in cheek) Why, I read it in the user manual for ACE/Jet of course. Only joking, there isn't one. Like anything in Jet 4.0, you just have experiment.
Decimal literals (with exceptions e.g. extremely large and extremely small values) are of type DECIMAL. For example:
SELECT TYPENAME(0.1)
returns 'Decimal'.
When using the numeric operators (add, subtract, multiply and divide) involving a value of type DECIMAL will coerce the result to type DECIMAL (the same exceptions as above apply).
A simple yet effective test is to create a table with one column for each of the numeric data types, insert a small value (say 1) for each, then add/subtract/multiply/divide all by a decimal literal (say 0.1):
SQL DDL:
CREATE TABLE TestNumericDataTypes
(
TINYINT_col TINYINT NOT NULL,
SMALLINT_col SMALLINT NOT NULL,
INTEGER_col INTEGER NOT NULL,
REAL_col REAL NOT NULL,
FLOAT_col FLOAT NOT NULL,
DECIMAL_col DECIMAL NOT NULL,
CURRENCY_col CURRENCY NOT NULL,
YESNO_col YESNO NOT NULL,
DATETIME_col DATETIME NOT NULL
);
SQL DML:
INSERT INTO TestNumericDataTypes
(
TINYINT_col, SMALLINT_col, INTEGER_col,
REAL_col, FLOAT_col, DECIMAL_col,
CURRENCY_col, YESNO_col, DATETIME_col
)
VALUES (1, 1, 1, 1, 1, 1, 1, 1, 1);
SQL DML:
SELECT TYPENAME(TINYINT_col * 0.1),
TYPENAME(SMALLINT_col * 0.1),
TYPENAME(INTEGER_col * 0.1),
TYPENAME(REAL_col * 0.1),
TYPENAME(FLOAT_col * 0.1),
TYPENAME(DECIMAL_col * 0.1),
TYPENAME(CURRENCY_col * 0.1),
TYPENAME(YESNO_col * 0.1),
TYPENAME(DATETIME_col * 0.1),
TYPENAME(TINYINT_col / 0.1),
TYPENAME(SMALLINT_col / 0.1),
TYPENAME(INTEGER_col / 0.1),
TYPENAME(REAL_col / 0.1),
TYPENAME(FLOAT_col / 0.1),
TYPENAME(DECIMAL_col / 0.1),
TYPENAME(CURRENCY_col / 0.1),
TYPENAME(YESNO_col / 0.1),
TYPENAME(DATETIME_col / 0.1),
TYPENAME(TINYINT_col + 0.1),
TYPENAME(SMALLINT_col + 0.1),
TYPENAME(INTEGER_col + 0.1),
TYPENAME(REAL_col + 0.1),
TYPENAME(FLOAT_col + 0.1),
TYPENAME(DECIMAL_col + 0.1),
TYPENAME(CURRENCY_col + 0.1),
TYPENAME(YESNO_col + 0.1),
TYPENAME(DATETIME_col + 0.1),
TYPENAME(TINYINT_col - 0.1),
TYPENAME(SMALLINT_col - 0.1),
TYPENAME(INTEGER_col - 0.1),
TYPENAME(REAL_col - 0.1),
TYPENAME(FLOAT_col - 0.1),
TYPENAME(DECIMAL_col - 0.1),
TYPENAME(CURRENCY_col - 0.1),
TYPENAME(YESNO_col - 0.1),
TYPENAME(DATETIME_col - 0.1)
FROM TestNumericDataTypes;
I'm not sure whether you can create all these types via the Access interface nad you may not know how to run SQL DDL so here's some vanilla VBA (Access not required e.g. can be run from Excel, no references required e.g. just copy and paste):
Sub TestAccessDecimals()
On Error Resume Next
Kill Environ$("temp") & "\DropMe.mdb"
On Error GoTo 0
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe.mdb"
With .ActiveConnection
Dim Sql As String
Sql = _
"CREATE TABLE TestNumericDataTypes" & vbCr & "(" & vbCr & " TINYINT_col TINYINT NOT NULL, " & vbCr & " SMALLINT_col SMALLINT NOT NULL, " & vbCr & " INTEGER_col INTEGER NOT NULL, " & vbCr & " REAL_col REAL NOT NULL, " & vbCr & " FLOAT_col FLOAT NOT" & _
" NULL, " & vbCr & " DECIMAL_col DECIMAL NOT NULL, " & vbCr & " CURRENCY_col CURRENCY NOT NULL, " & vbCr & " YESNO_col YESNO NOT NULL, " & vbCr & " DATETIME_col DATETIME NOT NULL" & vbCr & ");"
.Execute Sql
Sql = _
"INSERT INTO TestNumericDataTypes " & vbCr & "(" & vbCr & " TINYINT_col, SMALLINT_col, INTEGER_col, " & vbCr & " REAL_col, FLOAT_col, DECIMAL_col, " & vbCr & " CURRENCY_col, YESNO_col, DATETIME_col" & vbCr & ") " & vbCr & "VALUES (1, 1, 1, 1, 1, 1," & _
" 1, 1, 1);"
.Execute Sql
Sql = _
"SELECT TYPENAME(TINYINT_col * 0.1), " & vbCr & " TYPENAME(SMALLINT_col * 0.1), " & vbCr & " TYPENAME(INTEGER_col * 0.1), " & vbCr & " TYPENAME(REAL_col * 0.1), " & vbCr & " TYPENAME(FLOAT_col * 0.1)," & _
" " & vbCr & " TYPENAME(DECIMAL_col * 0.1), " & vbCr & " TYPENAME(CURRENCY_col * 0.1), " & vbCr & " TYPENAME(YESNO_col * 0.1), " & vbCr & " TYPENAME(DATETIME_col * 0.1)," & vbCr & " TYPENAME(TINYINT_col / 0.1)," & _
" " & vbCr & " TYPENAME(SMALLINT_col / 0.1), " & vbCr & " TYPENAME(INTEGER_col / 0.1), " & vbCr & " TYPENAME(REAL_col / 0.1), " & vbCr & " TYPENAME(FLOAT_col / 0.1), " & vbCr & " TYPENAME(DECIMAL_col / 0.1)," & _
" " & vbCr & " TYPENAME(CURRENCY_col / 0.1), " & vbCr & " TYPENAME(YESNO_col / 0.1), " & vbCr & " TYPENAME(DATETIME_col / 0.1)," & vbCr & " TYPENAME(TINYINT_col + 0.1), " & vbCr & " TYPENAME(SMALLINT_col +" & _
" 0.1), " & vbCr & " TYPENAME(INTEGER_col + 0.1), " & vbCr & " TYPENAME(REAL_col + 0.1), " & vbCr & " TYPENAME(FLOAT_col + 0.1), " & vbCr & " TYPENAME(DECIMAL_col + 0.1), " & vbCr & " TYPENAME(CURRENCY_col" & _
" + 0.1), " & vbCr & " TYPENAME(YESNO_col + 0.1), " & vbCr & " TYPENAME(DATETIME_col + 0.1)," & vbCr & " TYPENAME(TINYINT_col - 0.1), " & vbCr & " TYPENAME(SMALLINT_col - 0.1), " & vbCr & " TYPENAME(INTEGER_col" & _
" - 0.1), " & vbCr & " TYPENAME(REAL_col - 0.1), " & vbCr & " TYPENAME(FLOAT_col - 0.1), " & vbCr & " TYPENAME(DECIMAL_col - 0.1), " & vbCr & " TYPENAME(CURRENCY_col - 0.1), " & vbCr & " TYPENAME(YESNO_col" & _
" - 0.1), " & vbCr & " TYPENAME(DATETIME_col - 0.1)" & vbCr & "FROM TestNumericDataTypes;"
Dim rs
Set rs = .Execute(Sql)
MsgBox rs.GetString
End With
Set .ActiveConnection = Nothing
End With
End Sub
The result is Decimal in every case. Q.E.D.
A few exceptions alluded to earlier:
Decimal literals which are equal to their INTEGER value e.g.
SELECT TYPENAME(1.0)
returns 'Long' (which is the VBA equivalent of Jet 4.0's INTEGER type -- why it shows the VBA name and not the Jet name I don't know).
...except when the value is beyond the INTEGER range:
SELECT TYPENAME(10000000000)
returns 'Decimal'
...excpet when the value is beyond the DECIMAL range:
SELECT TYPENAME(1E29)
returns 'Double' (being the VBA equivalent of Jet's FLOAT).
In the positive range, operating on the value with a DECIMAL literal retains the type as FLOAT e.g.
SELECT TYPENAME(1E29 + 0.1)
returns 'Double(FLOAT`).
...whereas in the negative range it is coersed to DECIMAL
SELECT TYPENAME(1E-29 + 0.1)
returns 'Decimal'.
Coersion works differently when crossing bounds e.g. (noting that the upper bounds for INTEGER is 2,147,483,647):
SELECT TYPENAME(2147483648)
returns 'Decimal`
...whereas:
SELECT TYPENAME(2147483647 + 1.0)
returns 'Double' (FLOAT).
No doubt there are other exceptions I haven't stumbled upon.