Excel won't round, Access will

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

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



Bryan Loeper said:
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 -
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top