ca1358
asked on
Need help with syntax with select query
I need help with the syntax with this piece of code:
Src = SELECT Table1.[AS400 ID], Sum(Table1.LoanAmt) AS SumOfLoanAmt
FROM Table1
WHERE (((Table1.PoolCode)<>"GOVT " And (Table1.PoolCode)<>"G2BD" And (Table1.PoolCode)<>"G21A" And (Table1.PoolCode)<>"G23a" And (Table1.PoolCode)<>"G25A") AND ((Table1.RecDt) Between #6/19/2006# And #6/23/2006#))
GROUP BY Table1.[AS400 ID]
HAVING (((Table1.[AS400 ID])=[forms]![payup table]![AS400 #]));
I am using Excel and running Access Select query. Here is the whole SUB.
Any help would greatly be appreciated.
////////////////////////// ////////// ////////// //
Public Sub OpenADO()
Dim dbpath As String
Dim Src As String
Dim Connection As ADODB.Connection
Dim Col As Integer
Dim Recordset As ADODB.Recordset
Dim As400 As Integer
Dim A1 As Range
'Without Password
' dbpath = "Data Source=\\Dtcnas-002mandato ry\volume. mdb
Set conn = New ADODB.Connection
With conn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open dbpath
End With
'''''''''''''''''''''''''' '''''''''' '''''''''' '''''''''' '''''''
' Create RecordSet
Set Recordset = New ADODB.Recordset
With Recordset
' Filter
‘This is the piece I need help with
Src = SELECT Table1.[AS400 ID], Sum(Table1.LoanAmt) AS SumOfLoanAmt
FROM Table1
WHERE (((Table1.PoolCode)<>"GOVT " And (Table1.PoolCode)<>"G2BD" And (Table1.PoolCode)<>"G21A" And (Table1.PoolCode)<>"G23a" And (Table1.PoolCode)<>"G25A") AND ((Table1.RecDt) Between #6/19/2006# And #6/23/2006#))
GROUP BY Table1.[AS400 ID]
HAVING (((Table1.[AS400 ID])=[forms]![payup table]![AS400 #]));
.Open source:=Src, ActiveConnection:=conn
' Write the field names
For Col = 0 To Recordset.Fields.Count - 1
Sheet22.Range("r1").Offset (0, Col).Value = Recordset.Fields(Col).Name
Next
' Write the recordset
Sheet22.Range("r2").Offset (0, 0).CopyFromRecordset Recordset
End With
'''''''''''''''''''''''''' '''''''''' '''''''''' '''''''''' '''''''''' ''''''''''
Set Recordset = Nothing
conn.Close
Set conn = Nothing
End Sub
Src = SELECT Table1.[AS400 ID], Sum(Table1.LoanAmt) AS SumOfLoanAmt
FROM Table1
WHERE (((Table1.PoolCode)<>"GOVT
GROUP BY Table1.[AS400 ID]
HAVING (((Table1.[AS400 ID])=[forms]![payup table]![AS400 #]));
I am using Excel and running Access Select query. Here is the whole SUB.
Any help would greatly be appreciated.
//////////////////////////
Public Sub OpenADO()
Dim dbpath As String
Dim Src As String
Dim Connection As ADODB.Connection
Dim Col As Integer
Dim Recordset As ADODB.Recordset
Dim As400 As Integer
Dim A1 As Range
'Without Password
' dbpath = "Data Source=\\Dtcnas-002mandato
Set conn = New ADODB.Connection
With conn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open dbpath
End With
''''''''''''''''''''''''''
' Create RecordSet
Set Recordset = New ADODB.Recordset
With Recordset
' Filter
‘This is the piece I need help with
Src = SELECT Table1.[AS400 ID], Sum(Table1.LoanAmt) AS SumOfLoanAmt
FROM Table1
WHERE (((Table1.PoolCode)<>"GOVT
GROUP BY Table1.[AS400 ID]
HAVING (((Table1.[AS400 ID])=[forms]![payup table]![AS400 #]));
.Open source:=Src, ActiveConnection:=conn
' Write the field names
For Col = 0 To Recordset.Fields.Count - 1
Sheet22.Range("r1").Offset
Next
' Write the recordset
Sheet22.Range("r2").Offset
End With
''''''''''''''''''''''''''
Set Recordset = Nothing
conn.Close
Set conn = Nothing
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks for the grade. Good luck with your project. -Jim
ASKER