The Specified Field Could Refere to More Than One Table

[Chris1112]

I'm receiving this error when I attempt to run a query based on two tables. They do have the same field names but the SQL query shows them both as fully qualified. I've been researching this issue but not having much luck. Any help is greatly appreciated.

SQL:

SELECT [GHI#FILE_JOBMST1].SUBDIV, [GHI#FILE_JOBMST1].[JOB#], [GHI#FILE_JOBMST1].JBTYPE, [GHI#FILE_JOBMST1].MODEL, [GHI#FILE_JOBMST1].LOT, [GHI#FILE_JOBMST1].BLOCK, [GHI#FILE_JOBMST1].SECTN, [GHI#FILE_JOBMST1].ADDRS, [GHI#FILE_JOBMST1].STREET, [GHI#FILE_JOBMST1].START, [GHI#FILE_JOBMST1].CMPLT, [GHI#FILE_JOBMST1].POUR, [GHI#FILE_JOBMST1].CLSDTE, [GHI#FILE_JOBMST1].CLSPRC, [GHI#FILE_JOBMST1].SLSPRC, [GHI#FILE_JOBMST1].PURCH, [GHI#FILE_JOBMST1].SLSDTE, [GHI#FILE_JOBMST1].ECMPLT, [GHI#FILE_JOBMST1].MTGCO, [GHI#FILE_JOBMST1].[LOAN#], [GHI#FILE_JOBMST1].[LOAN$], [GHI#FILE_JOBMST1].DRAW, [GHI#FILE_JOBMST1].LOANDT, [GHI#FILE_JOBMST1].SUPER, [GHI#FILE_JOBMST1].SWING, [GHI#FILE_JOBMST1].PLAN, [GHI#FILE_JOBMST1].ELEV, [GHI#FILE_JOBMST1].WRKSHT, [GHI#FILE_JOBMST1].ALCINT, [GHI#FILE_JOBMST1].FNDDTE, [GHI#FILE_JOBMST1].[SALE#], [GHI#FILE_JOBMST1].VCHPST, [GHI#FILE_JOBMST1].LSTPRC, [GHI#FILE_JOBMST1].REMOVE, [GHI#FILE_JOBMST1].PLNMOD, [GHI#FILE_JOBMST1].LOANGL, [GHI#FILE_JOBMST1].WAREXP, [GHI#FILE_JOBMST1].COMMNT, [GHI#FILE_JOBMST1].[INTGL#], [GHI#FILE_JOBMST1].FILING, [GHI#FILE_JOBMST1].ALLOW, [GHI#FILE_JOBMST1].ENGNO1, [GHI#FILE_JOBMST1].ENGNO2, [GHI#FILE_JOBMST1].PERMIT, [GHI#FILE_JOBMST1].[SOIL#], [GHI#FILE_JOBMST1].FNDCDE, [GHI#FILE_JOBMST1].PRODOH, [GHI#FILE_JOBMST1].FRMSVY, [GHI#FILE_JOBMST1].ALWWIP, [GHI#FILE_JOBMST1].ALWWAR, [GHI#FILE_JOBMST1].PRJCLS, [GHI#FILE_JOBMST1].TAKDWN, [GHI#FILE_JOBMST1].BSTCM1, [GHI#FILE_JOBMST1].BSTCM2, [GHI#FILE_JOBMST1].BSTCM3, [GHI#FILE_JOBMST1].BSTCM4, [GHI#FILE_JOBMST1].[TRANS#], [GHI#FILE_JOBMST1].PLSRCD, [GHI#FILE_JOBMST1].SCHED, IsNumeric([JOB#]) AS Expr1, [GHI#FILE_PSDETL].STAGEFROM [GHI#FILE_JOBMST1] INNER JOIN [GHI#FILE_PSDETL] ON ([GHI#FILE_JOBMST1].[JOB#] = [GHI#FILE_PSDETL].[JOB#]) AND ([GHI#FILE_JOBMST1].SUBDIV = [GHI#FILE_PSDETL].SUBDIV)
WHERE ((([GHI#FILE_JOBMST1].START)>"0") AND (([GHI#FILE_JOBMST1].CLSDTE)="") AND (([GHI#FILE_JOBMST1].PURCH)<>"") AND ((IsNumeric([JOB#]))=True) AND (([GHI#FILE_PSDETL].STAGE)>="2"));

[pbaldy]

Is JOB# in both tables? By the way, spaces and symbols aren't a good idea in object names.

[Bob Fitz]

Could it be that the field JOB# needs to be qualified

[Bob Fitz]

Sorry Paul, didn't see your post.

[pbaldy]

No biggie Bob. Looks like we both spotted the same thing.

[Chris1112]

Yes, the field JOB# is in both tables, but the references to it in SQL are fully qualified.

I know symbols are bad design. Sadly this is not my beast. It's actually an AS400 antique database that I need to report from.

[Chris1112]

Unless my eyes are deceiving me, JOB# is qualified in the SQL code. Am I missing something? I've double-checked.

[pbaldy]

No, it isn't:


...AND ((IsNumeric([JOB#]))=True) AND...

[Bob Fitz]

What about:
...IsNumeric([JOB#]) AS Expr1...
shouldn't that also be qualified

[Chris1112]

Thank you very much Bob and Paul both. That did the trick. I'll get my eyes checked ASAP.

[pbaldy]

What about:
...IsNumeric([JOB#]) AS Expr1...
shouldn't that also be qualified

Absolutely. I assumed Chris would fix in design view, which would fix both places. Assumptions have a way of backfiring, so I should have been more clear.