Specified field ... could refer to more than one table...

G

Guest

I am getting this error even though there is only a single table having the
specified field name. It has something to do with the combination of an inner
join through an intermediate table to a third table (neither involving the
problematic field) and an IIf that references the problematic field.

Here is the SQL:

SELECT IIf([ContractType]="A","TypeA","TypeB") AS Type, Users.UserName
FROM (Users INNER JOIN UserRoles ON Users.UserID = UserRoles.UserID) INNER
JOIN Contracts ON UserRoles.UserRoleID = Contracts.TraderRoleID;

This (after being stripped down to the above statement) involves only three
tables: Contracts, UserRoles, and Users. The Contracts table holds a
reference to a UserRoleID, but I must look through the UserRoles table to the
Users table to get the user's name.

The problematic field is [ContractType] from the [Contracts] table. Removing
either the complex/once-removed inner join (Contracts.TraderRoleID ->
UserRoles.UserRoleID -> Users.UserID) or the IIf statement will correct it.
Of course, inserting the table name [Contracts]! before [ContractType] also
resolves the problem.

However, this all worked just last week, and now suddenly several instances
of this in different places in my application are having similar errors. Each
involves an IIf involving the specified field and a complex inner join (not
involving the specified field) through an intermediate table to a third table.

Is there some option that I have turned on that is causing this?
 
G

Guest

Never mind. I discovered that I had, indeed, renamed a field that resulted in
a duplicate.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top