Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2008
    Posts
    78

    Unanswered: Join Syntax column reference

    Code:
    Dim RSO as RecordSet
    Set RSO = CurrentDB.OpenRecordset("SELECT * FROM tblOrders LEFT JOIN Customers on Customers.CustID=tblOrders.CustID")
     
    RSO.MoveFirst
    
    msgBox RSO!tblOrders.CustID
    msgBox RSO!Customers.CustID
    
    Set RSO = Nothing
    When fieldnames are Identical between the tables, how can I differentiate them? The above code does not compile
    The join works, because when I msgBox a fieldname not shared between the tables, I get a response.


    I have tried:

    RSO!tblOrders.CustID
    RSO!CustID
    RSO![tblOrders]CustID
    RSO!tblOrders[CustID]

    No luck

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    don't use select *
    do it the longhanded way
    select my, column, list from my table
    use aliases for columns with the smae name

    eg
    select ID as CustomerID,.....
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Try

    msgBox RSO![tblOrders.CustID]
    Paul

  4. #4
    Join Date
    Aug 2008
    Posts
    78
    Quote Originally Posted by pbaldy
    Try

    msgBox RSO![tblOrders.CustID]
    yea doesn't like that either.

  5. #5
    Join Date
    Aug 2008
    Posts
    78
    Quote Originally Posted by healdem
    don't use select *
    do it the longhanded way
    select my, column, list from my table
    use aliases for columns with the smae name

    eg
    select ID as CustomerID,.....

    Right, that's the smarter way. Assume that's been done. How would you fully qualify the table + column name? The code becomes self documenting when you see :

    blahblah = RecordSet!tablename.fieldname

    At least you know which columns from which table(s) you're using

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Quote Originally Posted by PonPending
    yea doesn't like that either.
    I tested in a database and it worked, though I also disambiguated the declaration to:

    Dim RSO as DAO.RecordSet
    Paul

Posting Permissions

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