Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jan 2002
    Location
    Midlothian, Virginia, USA
    Posts
    874
    Thanks
    0
    Thanked 2 Times in 1 Post

    Outer Join in Recordset (Access 97)

    Can an Outer Join (LEFT JOIN or RIGHT JOIN) be used in a Recordset that will be used in VBA code as an input recordset? If so, how do I avoid the "Item not found in this collection" error when I try to access a field that is not there in that particular record?

    I hope I'm explaining this clearly enough. Obviously, if an SQL statement uses an outer join, then there are some fields in the resulting output that are present in some records and not present in other records. I haven't found any way in VBA code to retrieve the data from those fields when they are present and still avoid a run-time error when they are not present.

    Thanks,
    Bill

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

    Re: Outer Join in Recordset (Access 97)

    If you use an outer join, the fields from both sides will be available in the recordset, so you shouldn't get "Item not found in this collection". If there is no matching record, all fields from one side of the join will be Null.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2002
    Location
    Midlothian, Virginia, USA
    Posts
    874
    Thanks
    0
    Thanked 2 Times in 1 Post

    Re: Outer Join in Recordset (Access 97)

    Well, the Null fields when there is no matching record on one side of the join is certainly what I had hoped would happen. I have an actual query that works fine. I changed that query to SQL view and used copy and paste to move the SQL to the VBA module and did a little line folding so there can't be any misspelling or that kind of problem.

    I just played with it a little more and what I get is a Compile error, "Method or data member not found". That is the error I was getting at first too. I'm actually not sure how I got to the point where I got the run-time error that I reported in my original message! I've experimented with a number of things and I must have had a different problem then. For what it is worth, the record source on the side of the outer join that has a lot fewer records is a saved query.

    Any other ideas?

    Thanks, Hans.
    Bill

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

    Re: Outer Join in Recordset (Access 97)

    I'd have to see what you are playing with. Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Outer Join in Recordset (Access 97)

    When you manipulate SQL in the VBA editor it is easy to introduce small errors that can have a major impact. To test for this put in
    debug.print SQL after the SQL is finalised, but before the line that tries to use it.

    You can then copy the sql from the immediate window back into a new query and see if it works, and if not , try to find out what is wrong.
    Regards
    John



  6. #6
    5 Star Lounger
    Join Date
    Jan 2002
    Location
    Midlothian, Virginia, USA
    Posts
    874
    Thanks
    0
    Thanked 2 Times in 1 Post

    Re: Outer Join in Recordset (Access 97)

    That is a great way to check it. Thanks, John, for the suggestion. I try it.

    Bill

  7. #7
    5 Star Lounger
    Join Date
    Jan 2002
    Location
    Midlothian, Virginia, USA
    Posts
    874
    Thanks
    0
    Thanked 2 Times in 1 Post

    Re: Outer Join in Recordset (Access 97)

    I don't know if I can make a practical reduced version to show the problem. I'll have more time to think about that in a week or two and maybe I can come up with something.

    Thanks for the suggestion.

    Bill

Posting Permissions

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