MS Access join problem

[Muddmuse]

When running the following query against Access I get the "Item cannot be found in the collection corresponding to the requested name or ordinal" error.

SELECT u.id, u.name, u.active, ut.descr FROM tblUsers AS u LEFT OUTER JOIN tblUserTypes AS ut ON ut.userType = u.userType

Using the same query against SQLServer runs fine. Both db's have the same structure. What's up with Access?

 

[Chopstik]

Is this an Access issue or an ASP issue? In my experience with ASP, I usually receive this error when I am attempting to reference a variable that is not in my recordset (I've either typed it wrong or changed it somehow). If you're in the wrong forum, you may want to try forum181, else please offer more description with the problem in ASP.

------------------------------------------------------------------------------------------------------------------------
"I am not young enough to know everything."
Oscar Wilde (1854-1900)

 

[johnwm]

I believe that you don't use the OUTER in an Access query. For an OUTER join use:

SELECT u.id, u.name, u.active, ut.descr FROM tblUsers AS u LEFT JOIN tblUserTypes AS ut ON ut.userType = u.userType

See jetsql40.chm (should be on your local drive)

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller