Excel won't round, Access will
 

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?

Excel won't round, Access will
 

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

"Bryan Loeper" wrote:

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?

Excel won't round, Access will
 

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

On May 21, 7:43 pm, Mike wrote:
try this
Round([field5],2) As Field5



"Bryan Loeper" wrote:
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 -