"Join Opertation not supported" MS-ACCESS 2000/JET help with this query please?

[jesusw00t]

This Query Dose NOT work in Access 2000

SELECT Members.First_Name + ' ' + Members.Last_Name AS Member, iif(isNull(Friends.My_E_Mail), Friends.My_E_Mail, Friends.Friend_E_Mail) AS E_Mail, Members.First_Name AS Name 
FROM ((Members LEFT OUTER JOIN Friends ON Members.E_Mail = Friends.My_E_Mail AND Friends.Friend_E_Mail = ?) 
LEFT OUTER JOIN Friends Friends_1 ON Members.E_Mail = Friends.Friend_E_Mail AND Friends.My_E_Mail = ?)

Sample Tables

   Members(all VARCHAR)     SOME DATA
    First_Name               Alester Jude Carl Jones
    Last_Name                A       B    C    J
    
    FRIENDS(ALL VARCHAR)
    My_E_Mail               Alester@lam.com Alester@lam.com  Alester@lam.com
    Friend_E_Mail           jude@lam.com    carl@lam.com   jones@lam.com

Desired Output if ("?" in above query is: jones@lam.com)

   +--------------+-----------+------------+
    |Member        |E_Mail     |  Name      |
    +---------------------------------------+
     Alester A   Alester@lam.com  Alester

Desired Output if("?" in above query is: Alester@lam.com)

  +--------------+-----------+------------+
    |Member        |E_Mail     |  Name      |
    +---------------------------------------+
     Jude B        jude@lam.com  Jude
     carl C        carl@lam.com  Carl
     Jones J       jones@lam.com Jones

PS the "?" are query string parameters that im passing in the "?" i know that works fine.


MY QUESTION IS : i keep getting this error "JOIN OPERATION NOT SUPPORTED"

is their a work around query i can use without using a stored procedure or using multiple queries since this needs to be ONE SINGLE QUERY!?

Thanks

[boblarson]

It is just LEFT JOIN not LEFT OUTER JOIN.

[jesusw00t]

i know access changes it for me still have same problem "JOIN NOT SUPPORTED"

[jesusw00t]

anyone know?