Excel won't round, Access will

  • Thread starter Thread starter Bryan Loeper
  • Start date Start date
B

Bryan Loeper

Using the following SQL:

SELECT UCASE(field1) AS Field1, UCASE(field2) AS Field2, field3 AS
Field3, ROUND(1.076 * field4 / 3, 3) AS Field4, ROUND(field5, 2) AS
Field5
FROM myTable
WHERE (UCASE(field2) IN (myList))
ORDER BY 1,2,3;

with the following VBA:

Dim rs As DAO.Recordset
Dim qry As DAO.QueryDef
Set qry = Database.CreateQueryDef("", SQL)
qry.ODBCTimeout = 0
Set rs = qry.OpenRecordset(dbOpenDynaset, dbSeeChanges)


If I create a new query from within Access and use that SQL, Field5
rounds fine. If I use the VBA from within Excel (2003), then Field5
isn't rounded any more. Any ideas why?
 
Unfortunately, that didn't do the trick. Oddly, what did was:

Round([field5] / 1, 2) As Field5

I don't know why that had to be done that way though.

-Bryan

try this
Round([field5],2) As Field5



Bryan Loeper said:
Using the following SQL:
Click to expand...
SELECT UCASE(field1) AS Field1, UCASE(field2) AS Field2, field3 AS
Field3, ROUND(1.076 * field4 / 3, 3) AS Field4, ROUND(field5, 2) AS
Field5
FROM myTable
WHERE (UCASE(field2) IN (myList))
ORDER BY 1,2,3;
Click to expand...
with the following VBA:
Click to expand...
Dim rs As DAO.Recordset
Dim qry As DAO.QueryDef
Set qry = Database.CreateQueryDef("", SQL)
qry.ODBCTimeout = 0
Set rs = qry.OpenRecordset(dbOpenDynaset, dbSeeChanges)
Click to expand...
If I create a new query from within Access and use that SQL, Field5
rounds fine. If I use the VBA from within Excel (2003), then Field5
isn't rounded any more. Any ideas why?- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 
Back
Top