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

 

[dankunal]

I am still getting integers

 

[RoryA]

I assume you have verified it's not merely the cell format - it actually displays as integer in the formula bar?

 

[dankunal]

Yes, I did verify that it wasn't a formatting issue.

 

[RoryA]

If you remove the grouping and summing, do you still get integers in each record in the recordset?

 

[dankunal]

Yes, i still get an integer when i remove the sum and grouping.

 

[RoryA]

I suspect it's an issue with your DSN or the provider it uses but I don't use AS400.

 

[Norie]

dankumal

You said in an earlier post that you get the correct result when retrieving the data using MS Query.

If that's the case why not use it?

To code for that turn on the Macro Recorder and manually import the data using Data>From other sources...

 

[dankunal]

We did have an update in our IBM Client Access that started the change in all of my reports. IT told me that the update shouldn't have any effect on my reports and they felt like I was not declaring a certain variable correctly in my VBA code. Since I am not a vba expert I wanted to make sure that my code wasn't causing the problem. So you guys don't seen anything wrong with my code?

 

[Norie]

There's nothing wrong with your declarations and they wouldn't cause the behaviour you describe anyway.

Did you try my suggestion of recording a macro when using MS Query to get the results of the query?

 

[dankunal]

I do use a macro to refresh MS query on some of my reports but the vast majority of my reports already has the code in question. Thanks for everyone's help...I just wanted to make sure my code wasn't doing something wrong since I am not an expert.