Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Feb 2001
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    docmd.openquery (97)

    I have attached the cope I am trying to run. I am not sure if this can be done.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: docmd.openquery (97)

    I'd assemble the SQL statement you need in code, then use db.Execute to execute it.

  3. #3
    Lounger
    Join Date
    Feb 2001
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: docmd.openquery (97)

    I thought the same thing but the SQL code is complex and I can't seem to get past the errors, actually I am really not sure how to do it that way.

    Thanks for all of your help

    INSERT INTO TOPNFSACCOUNTS ( Principal, AccountNo, RepExec, address1, address2, address3, address4, address5, address6 )
    SELECT TOP 25 [Qry: Positions].SumOfPrincipal AS Principal, NFSNameandAddress101.AccountNo, NFSNameandAddress101.RepExec, NFSNameandAddress101.address1, NFSNameandAddress101.address2, NFSNameandAddress101.address3, NFSNameandAddress101.address4, NFSNameandAddress101.address5, NFSNameandAddress101.address6
    FROM NFSNameandAddress101 INNER JOIN [Qry: Positions] ON NFSNameandAddress101.AccountNo = [Qry: Positions].AccountNo
    WHERE (((NFSNameandAddress101.RepExec)=[repnumber]))
    ORDER BY [Qry: Positions].SumOfPrincipal DESC;

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: docmd.openquery (97)

    You'd use something like this:
    <code>
    Dim strSQL As String
    strSQL = "INSERT INTO TOPNFSACCOUNTS ( Principal, AccountNo, RepExec, address1, address2, " & _
    "address3, address4, address5, address6 ) SELECT TOP 25 [Qry: Positions].SumOfPrincipal AS " & _
    "Principal, NFSNameandAddress101.AccountNo, NFSNameandAddress101.RepExec, " & _
    "NFSNameandAddress101.address1, NFSNameandAddress101.address2, " & _
    "NFSNameandAddress101.address3, NFSNameandAddress101.address4, " & _
    "NFSNameandAddress101.address5, NFSNameandAddress101.address6 " & _
    "FROM NFSNameandAddress101 INNER JOIN [Qry: Positions] ON " & _
    "NFSNameandAddress101.AccountNo = [Qry: Positions].AccountNo " & _
    "WHERE NFSNameandAddress101.RepExec=" & repnumber & _
    " ORDER BY [Qry: Positions].SumOfPrincipal DESC"
    db.Execute strSQL, dbFailOnError
    </code>
    This assumes that RepExec is a number field. If it is a text field:
    <code>
    ...
    "WHERE NFSNameandAddress101.RepExec=" & Chr(34) & repnumber & Chr(34) & _
    ...</code>

  5. #5
    Lounger
    Join Date
    Feb 2001
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: docmd.openquery (97)

    That works perfectly
    one more question, the do until loop does not loop through all the records, it keeps processing the same rep number over and over. Should I be doing is another way?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: docmd.openquery (97)

    Immediately above the line Loop, insert this line:

    rstrep.MoveNext

    This moves the recordset to the next record.

  7. #7
    Lounger
    Join Date
    Feb 2001
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: docmd.openquery (97)

    PERFECT THANK YOU VERY MUCH!!!!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •