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:

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

dankunal

New Member
Joined
Feb 4, 2014
Messages
10
I am pulling the information throught a SQL code called "SQL_Text" which is defined as a string.
 

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,119
Does changing string to double or long help (assuming your data is just numbers) ?
 

dankunal

New Member
Joined
Feb 4, 2014
Messages
10

ADVERTISEMENT

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.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
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?
 

dankunal

New Member
Joined
Feb 4, 2014
Messages
10

ADVERTISEMENT

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>
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
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
 

dankunal

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

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
It's Set rngDst.Offset(1).
 

Watch MrExcel Video

Forum statistics

Threads
1,122,481
Messages
5,596,399
Members
414,063
Latest member
N_Bates

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
Top