Results 1 to 8 of 8
  1. #1
    George is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2012
    Posts
    295

    Vetting a query

    Good Day All,



    Can someone please vet the following query, as it is triggering an error message although it seems correctly written.

    HTML Code:
        sqlStrg = "SELECT [Cash posting Main Table].*, [Cash posting Sub Table].*" _
    & "FROM [Cash posting Main Table] LEFT JOIN [Cash posting Sub Table] ON [Cash posting Main Table].MainPostingID = [Cash posting Sub Table].MainPostingID " _
    & "WHERE ((([Cash posting Main Table].MainPostingID)=23));"
    
       
        For i = 1 To NumOfRows
      ACName = Nz(rst.Fields("Description"), "")
       Debit = rst.Fields("Debit")
       
        rst.MoveNext
        Next i
    The first problem is that the NumOfRows returns a value of 1. This is incorrect when checked otherwise.
    Second, the error message states "Item not found in the Collection"
    These field names are verified.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    There must be more to the code. This may help with the SQL:

    http://www.baldyweb.com/ImmediateWindow.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Need a space at the end of the line...
    Code:
    sqlStrg = "SELECT [Cash posting Main Table].*, [Cash posting Sub Table].* " _

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I suggest you set up a

    Debug.Print sqlStrg
    before trying to use the SQL in your process. This will show you how Access has rendered/understood your statement.

    This is a good practice for design and debugging.

    Good luck.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by orange View Post
    I suggest you set up a

    Debug.Print sqlStrg
    before trying to use the SQL in your process. This will show you how Access has rendered/understood your statement.

    This is a good practice for design and debugging.

    Good luck.
    As detailed in the link I posted.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    As Paul said, you have to have left something (a lot, maybe) out of that code.

    - You assign the SQL to the variable sqlStrg, but then you don't use it anywhere.
    - How do you get a value for NumofRows?
    - what is rst (I know it's a recordset, but what's in it?)

    The error message you get is not one you would typically get from a query.

    More likely is that the rst recordset does not contain fields "Description" or "Debit"

  7. #7
    George is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2012
    Posts
    295
    Thanks All for you assistances.

    The problem was that I had joined 2 tables with identical field names and this was confusing the query because I was not using the expanded field name in the query. The table names had to prefix each field name.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Glad you found the problem.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums