VBA Excel & ADO Connection Changing numbers into integers

[dankunal]

I have a vba code that connects and pulls information from AS400, but for some reason it changes my field ((GLPMF.GLMP01) as amount) to integer instead of a decimal. For example the amount should be 619.60 but it pulls in 619.00. Is there a way to fix this? Part of my code is below.

Thanks,

Dan

'-----------------------------------------------------------------------------------
'Variables Declaration
'-----------------------------------------------------------------------------------

Dim SQL_Text As String
Dim cnnConn As ADODB.Connection
Dim rstRecordset As ADODB.Recordset
Dim cmdCommand As ADODB.Command
Dim SQL_Insert_01 As Variant


'-----------------------------------------------------------------------------------
' Open the connection.
'-----------------------------------------------------------------------------------





Set cnnConn = New ADODB.Connection
With cnnConn
.ConnectionString = _
"DSN=WRIDATA; UID=" & UserName & "; PWD=" & Password & ";"
.Open
End With
'-----------------------------------------------------------------------------------
'SQL
'-----------------------------------------------------------------------------------


SQL_Text = _
"SELECT GLPMF.GLMCO,GLPMF.GLMPJ, GLPMF.GLMGL, GLPGL.GLGLNM, GLPMF.GLMBK, GLPMF.GLMFY,sum(GLPMF.GLMP01)as amount " & _
" FROM WRI2.GLPMF GLPMF INNER JOIN WRI2.GLPGL GLPGL ON GLPMF.GLMGL = GLPGL.GLGL " & _
" WHERE (GLPMF.GLMCO IN ('225')" & ") AND (GLPMF.GLMBK IN ('ACT')" & ") AND (GLPMF.GLMFY=114) " & _
" GROUP BY GLPMF.GLMCO,GLPMF.GLMPJ, GLPMF.GLMGL, GLPGL.GLGLNM, GLPMF.GLMBK, GLPMF.GLMFY "


'MsgBox (SQL_Text)



'-----------------------------------------------------------------------------------
' Open the recordset.
'-----------------------------------------------------------------------------------

Set rs = New ADODB.Recordset
Set rs.ActiveConnection = cnnConn
rs.Open SQL_Text
'-----------------------------------------------------------------------------------
'Retrieves the data to the sheet. The sheet should be called "Sheet1"
'-----------------------------------------------------------------------------------

Sheets("Data GL").Cells(2, 1).CopyFromRecordset (rs)
Sheets("Data GL").Activate

 

[Norie]

dankunal

Have you tried what I suggested?

It might help us to find out what the problem is if we see the code generated when you record a macro when using MS Query to execute the query.