VBA Excel & ADO Connection Changing numbers into integers

dankunal

New Member
Joined
Feb 4, 2014
Messages
10
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

Rich (BB code):
'-----------------------------------------------------------------------------------
'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
 
Last edited by a moderator:

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
I am pulling the information throught a SQL code called "SQL_Text" which is defined as a string.
 
Upvote 0
I believe I have to keep keep the SQL_TEXT has a string in order to pull it through an ADO connection. I tried changing it to double and it gets me an error message.
 
Upvote 0
Your query doesn't return GLPMF.GLMP01 it returns a sum of GLPMF.GLMP01.

What results do you get if you run the query in the original database?
 
Upvote 0
Norie-You are correct, but I don't think the "sum of GLPMF.GLMP01" is causing my problem.

If I run it through microsoft query I get a number (619.60), it's only through VBA I get an integer (619.00). </SPAN>
 
Upvote 0
What happens if you use this to transfer the data to the sheet rather than CopyFromRecordset.
Code:
Set rngDst =Sheets("Data GL").Cells(2, 1)
 
rs.MoveFirst

While Not rs.EOF
    For I = 0 To rs.Fields.Count-1
        rngDst.Offset(,I) = rs.Fields(I).Value
    Next I

    rs.MoveNext
    Set rngDst.Offset(1)
Wend
 
Upvote 0
I copied and pasted your code right after my recordset code (I am not familar with transfer data code) and I got a "Run time error 438: Object does support this property or method" when I get to the "rngDst.Offset (1)" Any advice on what I should correct?


Set rs = New ADODB.Recordset
Set rs.ActiveConnection = cnnConn
rs.Open SQL_Text

Set rngDst = Sheets("Data GL").Cells(2, 1)

rs.MoveFirst
While Not rs.EOF
For I = 0 To rs.Fields.Count - 1
rngDst.Offset(, I) = rs.Fields(I).Value
Next I
rs.MoveNext
rngDst.Offset (1)
Wend
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top