The specified field could refer to more than one table listed in the FROM clause

[jtidwell]

I am getting this error after running my query

The specified field [contractnum] could refer to more than one table listed in the FROM clause of your SQL statement. (Error 3079)

my contractnum is a one to many to another table.

how do i fix this?

[NeoPa]

You have (accidentally) posted this question in the Access Articles section. This is NOT an article.
I'm moving this to the main Access questions forum.

MODERATOR.

[NeoPa]

I suspect this is quite straightforward, but posting a copy of the SQL that is triggering the error message would prove helpful.
From there we can tell you precisely what to fix.

[nico5038]

You'll have to "qualify" the field [contractnum] as it's apperently part of two tables.
Let's assume you have the tables tblA and tblB, then use e.g.:
[tblA].[contractnum]
When it's tblA having the needed value. For a full (INNER) join the table won't matter, but when you have an outer (LEFT or RIGHT) join (there's an arrow displayed instead of a line), then make sure you use the "master" table to qualify the field

Nic;o)

[jcgads]

I get the same error message (the Datasheet View works). My SQL is

SELECT CONTRACT_ITEMS.EXP_DATE, Format([CONTRACT_ITEMS.EXP_DATE],"mmmm") AS Expr1, Format([CONTRACT_ITEMS.EXP_DATE],"mm") AS Expr2, Trim([ENTITY_1.FIRST_NAME]) & " " & Trim([ENTITY_1.LAST_NAME]) AS Expr3, CONTRACT_BUDGETS.FISCAL_YEAR, CONTRACT_BUDGETS.FYR_NOTES, ENTITY.LAST_NAME, CONTRACT_SPACE.CONTRACT_DESC, CONTRACT_SPACE.CONTRACT_TYPE, CONTRACT_SPACE.RENEWAL_DATE, CONTRACT_BUDGETS.FYR_BUDGET, CONTRACT_SPACE.EXP_DATE, CONTRACT_BUDGETS.FYR_SPENT, ENTITY_1.FIRST_NAME, ENTITY_1.LAST_NAME, CONTRACT_ITEMS.COMMENT
FROM (ENTITY AS ENTITY_1 RIGHT JOIN ((ENTITY INNER JOIN (CONTRACT_SPACE LEFT JOIN CONTRACT_ITEMS ON CONTRACT_SPACE.CONTRACT_ID = CONTRACT_ITEMS.CONTRACT_ID) ON ENTITY.ID = CONTRACT_SPACE.VENDOR_ID) LEFT JOIN CONTRACT_OWNERS ON CONTRACT_SPACE.CONTRACT_ID = CONTRACT_OWNERS.CONTRACT_ID) ON ENTITY_1.ID = CONTRACT_OWNERS.OWNER_ID) LEFT JOIN CONTRACT_BUDGETS ON CONTRACT_SPACE.CONTRACT_ID = CONTRACT_BUDGETS.CONTRACT_ID
WHERE (((Format([CONTRACT_ITEMS.EXP_DATE],"mmmm"))>=Format(Now(),"mmmm")) AND ((Format([CONTRACT_ITEMS.EXP_DATE],"mm"))>=Format(Now(),"mm")-2) AND ((CONTRACT_BUDGETS.FISCAL_YEAR)=Year(Now())));

Any help would be appreciated.