megatronixs
Registered User.
- Local time
- Today, 16:33
- Joined
- Aug 17, 2012
- Messages
- 719
Hi all,
I have been using a SQL query in access that now I need to convert into Ms Query.
I have the following:
this will not work in Ms Query (the query tool that forms part of Excel)
I have made the below code in excel to query a Teradata database.
I'm also having trouble to to pass the value from "Acc" and "branch" and have it only hard coded to test the rest of code.
Any possible help with this?
Greetings.
I did also post it in excel forum: http://www.excelforum.com/excel-pro...ative-numbers-from-2-column-to-2-columns.html
I have been using a SQL query in access that now I need to convert into Ms Query.
I have the following:
Code:
IIf([TRANS_AMT]>0,[TRANS_AMT],0)
I have made the below code in excel to query a Teradata database.
I'm also having trouble to to pass the value from "Acc" and "branch" and have it only hard coded to test the rest of code.
Code:
Private Sub Test()
Dim conn As ADODB.Connection
Dim rec1 As ADODB.Recordset
Dim thisSql As String
Dim FromDate As String
Dim ToDate As String
Dim Acc As String
Dim branch As String
Dim LastCol As Long
LastCol = Cells(2, Columns.Count).End(xlToLeft).Column + 1
LastCol2 = Cells(2, Columns.Count).End(xlToLeft).Column + 5
FromDate = Sheet1.Range("B6").Value
ToDate = Sheet1.Range("C6").Value
Set conn = New ADODB.Connection
conn.Open "DSN=DSN_Name;Databasename=db_name;Uid=user;Pwd=********;"
thisSql = "SELECT TRANS_DATE AS ""Date"", TRANS_NAME_ABBR_3 AS ""Type"", TRANS_NARR As ""Description"", TRANS_AMT AS ""Debit"" " & _
"db_name.FieldName WHERE ((TRANS_DATE) Between " & FromDate & " And " & ToDate & ")AND ((BRANCH_NO)=111111) AND ((ACC_NO)=1111)"
Set rec1 = New ADODB.Recordset
rec1.Open thisSql, conn
With Sheet2.QueryTables.Add(Connection:=rec1, Destination:=Cells(1, LastCol))
.Name = "data"
.FieldNames = True
.Refresh BackgroundQuery:=False
End With
Cells(1, LastCol2).Value = "Debit"
Cells(1, LastCol2 + 1).Value = "Debit"
Dim Lr As Long
Dim i As Long
Lr = Cells(Rows.Count, "E").End(xlUp).Row
For i = 2 To Lr
If Cells(i, 5).Value < 0 Then
Cells(i, 6).Value = Cells(i, 5).Value
Else
Cells(i, 7).Value = Cells(i, 5).Value
End If
Next i
End Sub
Greetings.
I did also post it in excel forum: http://www.excelforum.com/excel-pro...ative-numbers-from-2-column-to-2-columns.html