Problem with decimal number smaller than 1

  • Thread starter Thread starter Mlaky
  • Start date Start date
M

Mlaky

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.
 
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)?
 
onedaywhen said:
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)?
Click to expand...

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
 
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.

--
 
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.
Click to expand...

I change data type from decimal to single and this solve my problem.

Thank you very much!


Mlaky
 
Back
Top