Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with decimal number smaller than 1
Hi everyone!
I'm accessing MS Access database trough MS Excel. I have problem with decimal number smaller than 1. Example: For "0.456" I retrive ".456" from database in Excel recordset. Is there some way that ODBC driver convert ".456" value to "0.456" for computation or I have to do it my self in program? Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with decimal number smaller than 1
What is your table schema, in particular what is the data type for
this column? How are you accessing the database (e.g. ADO or DAO in code)? Are you sure you are using ODBC (i.e. post your connection string)? -- "Mlaky" wrote in message ... Hi everyone! I'm accessing MS Access database trough MS Excel. I have problem with decimal number smaller than 1. Example: For "0.456" I retrive ".456" from database in Excel recordset. Is there some way that ODBC driver convert ".456" value to "0.456" for computation or I have to do it my self in program? Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with decimal number smaller than 1
"onedaywhen" wrote in message
m... What is your table schema, in particular what is the data type for this column? How are you accessing the database (e.g. ADO or DAO in code)? Are you sure you are using ODBC (i.e. post your connection string)? Data type is decimal. I'm accessing to database with ODBC DAO. Code: Public wrk As Workspace Public co1 As Connection Public qd1 As QueryDef Public rs1 As Recordset Sub SqlConnect() Set wrk = CreateWorkspace("", "informix", "info93", dbUseODBC) Set co1 = wrk.OpenConnection("", , True, "ODBC;DSN=partner_a") Set qd1 = co1.CreateQueryDef("") qd1.ODBCTimeout = 0 End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with decimal number smaller than 1
I did some playing. I added a column to an existing Jet (.mdb) DB
using the MS Access application (I'd usually instead use DDL), chose data type 'Decimal' and set some of the other options e.g. precision = 10, scale = 4, decimal places = 4 - I didn't expect it to be so involved! (I think I'd usually just go for 'Single'.) I added some values, including 0.456. I then queried the data using DAO and, sure enough, the value was returned as .456. I looked at the underlying data type e.g. in the Immediate Window: ? rs1("MyNewField").Type = dbDecimal returned 'False' but ? rs1("MyNewField").Type = dbGUID returned 'True'. I guess it is cast as GUID because of the complex definition of decimal when set with the MS Access app. I then queried the same data with ADO. This time the value was returned as 0.456 and the underlying value adNumeric. Conclusions: change the column's datatype (e.g. to single) or use ADO. If neither is possible, try posting in one of the Micorsoft.Public.Access.* newsgroups. -- "Mlaky" wrote in message ... "onedaywhen" wrote in message m... What is your table schema, in particular what is the data type for this column? How are you accessing the database (e.g. ADO or DAO in code)? Are you sure you are using ODBC (i.e. post your connection string)? Data type is decimal. I'm accessing to database with ODBC DAO. Code: Public wrk As Workspace Public co1 As Connection Public qd1 As QueryDef Public rs1 As Recordset Sub SqlConnect() Set wrk = CreateWorkspace("", "informix", "info93", dbUseODBC) Set co1 = wrk.OpenConnection("", , True, "ODBC;DSN=partner_a") Set qd1 = co1.CreateQueryDef("") qd1.ODBCTimeout = 0 End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with decimal number smaller than 1
Conclusions: change the column's datatype (e.g. to single) or use ADO. If neither is possible, try posting in one of the Micorsoft.Public.Access.* newsgroups. I change data type from decimal to single and this solve my problem. Thank you very much! Mlaky |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
when I type a long number it shows up as smaller number and + | Excel Discussion (Misc queries) | |||
Format number to display % symbol smaller than onther text in cell | Excel Discussion (Misc queries) | |||
Converting 2-place decimal value to floating point decimal number with leading zero | Excel Discussion (Misc queries) | |||
geting the smaller number in a line?? | Excel Discussion (Misc queries) | |||
How to convert a decimal number to a non-decimal number? | Excel Worksheet Functions |