How to return as decimal field from a recordset in excel

[kmchar]

Hi

In an informix database I have two fields, with the same value in both, one of them is text base eg char(10). The other is decimal base eg decimal(14)

I am using ADODB in Excel97 to connect to the database and return the fields values into a recordset.

The returned value for the text base field is as expected
eg rs.fields(0).value returns 1467

But!!!

The returned value for the decimal base field is either zero, or a truncated version of the expected number.
eg rs.fields(1).value returns 146

Do I have to set the recordset field to handle the level of precision for the decimal???

Any help would be greatly appreciated


Thanks
Kelvin

 

[fredericofonseca]

Can you please post your code? Including connection string.

Depending on how you are doing it you may be defining the fields wrongly.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[kmchar]

Hi Frederico, here are sections of the code as you requested:

Option Explicit
Global cn As New ADODB.Connection
Global rs As New ADODB.Recordset

Sub Start()
Dim lp as Integer
lp = 0

cn.ConnectionString = "metrix"
cn.Provider = "msdatashape"
cn.CursorLocation = adUseNone
cn.Open

SQL1 = "Select distinct qa_sample.job_no, qa_test.result_text, qa_test.result from qa_sample, qa_test where qa_sample.qa_sample_no = qa_test.qa_sample_no and qa_sample.job_no = '0402/221'"

rs.Open SQL1, cn, adOpenForwardOnly, adLockReadOnly
rs.Sort = " job_no Asc"
rs.Filter = adFilterNone

While Not rs.EOF
Range("A1").Offset(lp, 0).Value = Trim(rs.fields(0).Value)
Range("A1").Offset(lp, 1).Value = Trim(rs.fields(1).Value)
Range("A1").Offset(lp, 2).Value = Trim(rs.fields(2).Value)

lp = lp + 1
rs.MoveNext
Wend
cn.Close
End Sub

The connection string 'metrix' is setup in the Informix ODBC driver setup, under 'Data Sources (ODBC)' in the control panel/Administrative Tools section, its settings are as follows:

Server Name : live_root
Hostname : a local IP address
Service : sqlturbo
Protocol : olsoctcp
Database name : metrix
Client Locale : en_US.CP1252
Database Locale : en_US.CP1252
Cursor Behaviour : 0 - Close
VMB character : 0 - Estimate
Fetch Buffer Size : 4096



This is what gets returned when the query is run:

Job_No Result_Text Result
0402/221 1100 110
0402/221 1300 130
0402/221 1500 150
0402/221 16.5 1
0402/221 1600 160

Result_Text is a character field in the database, and Result is a decimal field in the database and this is what I am struggling with, as it should be the same as Result_Text

I notice that I haven't at anytime described rs.Fields, or set it as a string, decimal etc. Do you need to do this?

Thanks Kelvin

 

[PHV]

And what gets returned in dbaccess ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244