Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I access the access data via Excel 2002 with auto update ? | Excel Programming | |||
VB Function Round vs Excel function Round not behaving the same Od | Excel Programming | |||
Importing data from Access to Excel, but I need to vary the table from Access | Excel Programming | |||
export access to excel. change access & update excel at same time | Excel Discussion (Misc queries) | |||
How do I ROUND() round off decimals of a column dataset? | Excel Worksheet Functions |