using Access sql in Ms Query

megatronixs

Registered User.
Local time
Today, 21:17
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:
Code:
IIf([TRANS_AMT]>0,[TRANS_AMT],0)
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.

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
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
 
when I use the below sql:
Code:
SELECT Table1.Credit_Date, Table1.Branch_nr, Table1.Account_nr, IIf(Amount>0,Amount) AS 'Debit', IIf(Amount<0,Amount) AS 'Credit'
FROM `C:\Users\Me\Documents\Database2.mdb`.Table1 Table1
WHERE (Table1.Credit_Date Between '20130101' And '20130117') AND (Table1.Branch_nr='0101') AND (Table1.Account_nr='101827')
then I get some resulst from the split into 2 columns.

I used the Ms Query and got lucky.
 
this is how far I got today:

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
branch = Sheet1.Range("D6").Value
 
Set conn = New ADODB.Connection
 ' "ODBC;DSN=MS Access Database;DBQ=C:\Users\Me\Documents\Database2.mdb;DefaultDir=C:\Users\Me\Documents;DriverId=25;FIL=MS Access;M"
conn.Open "DSN=MS Access Database;DBQ=C:\Users\Me\Documents\Database2.mdb;DefaultDir=C:\Users\Me\Documents;DriverId=25;FIL=MS Access;M"
 
 
'SELECT Table1.Credit_Date AS 'Date', Table1.Branch_nr AS 'Branch', Table1.Account_nr AS 'Account', IIf(Amount>0,Amount), IIf(Amount<0,Amount)
'FROM `C:\Users\Me\Documents\Database2.mdb`.Table1 Table1
'WHERE (Table1.Credit_Date Between '20130101' And '20130117') AND (Table1.Branch_nr='0101') AND (Table1.Account_nr='101827')
 
 
thisSql = "SELECT Table1.Credit_Date AS 'Date', Table1.Branch_nr AS 'Branch', Table1.Account_nr AS 'Account', IIf(Amount>0,Amount) As Debit, IIf(Amount<0,Amount) " & _
"FROM `C:\Users\Me\Documents\Database2.mdb`.Table1 Table1 " & _
"WHERE (Table1.Credit_Date Between '20130101' And '20130117')"
'"WHERE (Table1.Credit_Date Between '20130101' And '20130117') And (Table1.Branch_nr " & branch & ")"

Set rec1 = New ADODB.Recordset
rec1.Open thisSql, conn
 

With ActiveSheet.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

Code:
SELECT Table1.Credit_Date AS 'Date', Table1.Branch_nr AS 'Branch', Table1.Account_nr AS 'Account', IIf(Amount>0,Amount), IIf(Amount<0,Amount)
FROM `C:\Users\me\Documents\Database2.mdb`.Table1 Table1
WHERE (Table1.Credit_Date Between '20130101' And '20130117') AND (Table1.Branch_nr='0101') AND (Table1.Account_nr='101827')
 

Users who are viewing this thread

Back
Top Bottom