Excel won't round, Access will

[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?

 

[Guest]

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

 

[Bryan Loeper]

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

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?- Hide quoted text -

- Show quoted text -