?
Solved

Need help with syntax with select query

Posted on 2006-06-19
3
Medium Priority
?
230 Views
Last Modified: 2010-04-30
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-002mandatory\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
0
Comment
Question by:ca1358
  • 2
3 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 16936561
Src = "SELECT Table1.[AS400 ID], Sum(Table1.LoanAmt) AS SumOfLoanAmt "
Src = Src & "FROM Table1 "
Src = Src & "WHERE Table1.PoolCode NOT IN ('GOVT', 'G2BD', 'G21A', 'G23a', 'G25A') "
Src = Src & "AND Table1.RecDt Between #6/19/2006# And #6/23/2006# "
Src = Src & "GROUP BY Table1.[AS400 ID] "
Src = Src & "HAVING Table1.[AS400 ID]=" & [forms]![payup table]![AS400 #] & ";"
       
0
 

Author Comment

by:ca1358
ID: 16936586
Thank you!!
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 16936597
Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month8 days, 18 hours left to enroll

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question