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.

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.

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

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

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