The specified field could refer to more than one table listed in theFROM clause of your SQL statemen

[John W.]

I'm developing a form based on the following query that joins a number
of tables on the field [Casenum]. I'm getting the error message that
"The specified field could refer to more than one table listed in the
FROM clause of your SQL statement."

Here's the SQL statement:

SELECT
CLIENTSW.CASENUM,
CLIENTSW.PCODE,
CLIENTSW.DOPEN,
CLIENTSW.FUNDSNUM,
CLIENTSW.DCLOSED,
CLIENTSW.RCLOSED,
CLIENTSW.IMPACT,
CLIENTSW.PHELPED,
CLIENTSW.RECOVERY,
CLIENTSW.MAINBENEFIT,
CLIENTSW.HANDICAPPED,
CLIENTSW.RECOVERYM,
CLIENTSW.AVOID,
CLIENTSW.AVOIDM,
CLIENTSW.GoodStory,
zNotes.Note1,
[clientsw]![cfname] & " " & [clientsw]![cmi] & " " & [clientsw]!
[cLNAME] AS [Client Name],
QOutcomes_for_checkboxes.[Achieved (Won)],
QOutcomes_for_checkboxes.[Partially Achieved (Mixed Result)],
QOutcomes_for_checkboxes.[Did Not Achieve (Lost)],
QOutcomes_for_checkboxes.Underserved,
[Qclosing codes for check boxes].[Counsel and Advice],
[Qclosing codes for check boxes].[Limited Service],
[Qclosing codes for check boxes].[Neg Settle wo Lit],
[Qclosing codes for check boxes].[Neg Settle w Lit],
[Qclosing codes for check boxes].[Admin Decision],
[Qclosing codes for check boxes].[Court Decision Uncontested],
[Qclosing codes for check boxes].[Court Decision contested],
[Qclosing codes for check boxes].[Court Decision Appeal],
[Qclosing codes for check boxes].Other,
[Qclosing codes for check boxes].[Extensive Services],
zMainBenefits.MAINBENEFITB,
zMainBenefits.MAINBENEFITC,
PBI.ATNUM, PBI.PBIHOURS,
SMEMBER.SINITIALS

FROM
(((((CLIENTSW
LEFT JOIN zNotes ON CLIENTSW.CASENUM = zNotes.CASENUM)
LEFT JOIN QOutcomes_for_checkboxes ON CLIENTSW.CASENUM =
QOutcomes_for_checkboxes.CASENUM)
LEFT JOIN [Qclosing codes for check boxes] ON CLIENTSW.CASENUM =
[Qclosing codes for check boxes].CASENUM)
LEFT JOIN zMainBenefits ON CLIENTSW.CASENUM = zMainBenefits.CASENUM)
LEFT JOIN PBI ON CLIENTSW.CASENUM = PBI.CASENUM)
INNER JOIN SMEMBER ON CLIENTSW.SNUM = SMEMBER.SNUM;


Any suggestions as to where I might be going wrong, and how I can fix
this? Thanks.

 

[Dale Fye]

You don't already have a [Client Name] field in one of your tables do you?

Try using a dot (.) rather than a bang (!) in the section where you are
concatenating:

[CLIENTSW].[CFNAME] & " " & [CLIENTSW].[CMI] & " " & [CLIENTSW].[CLNAME]

HTH
Dale

 

[John W. Vinson]

I'm developing a form based on the following query that joins a number
of tables on the field [Casenum]. I'm getting the error message that
"The specified field could refer to more than one table listed in the
FROM clause of your SQL statement."

Try replacing ! with . in

[clientsw]![cfname] & " " & [clientsw]![cmi] & " " & [clientsw]!
[cLNAME] AS [Client Name],

The ! delimiter is for form objects (or other types of objects); you can
sometimes get away with it in SQL but the correct table.field specification
uses a period as a delimiter.

John W. Vinson [MVP]

 

[John W.]

You don't already have a [Client Name] field in one of your tables do you?

Try using a dot (.) rather than a bang (!) in the section where you are
concatenating:

[CLIENTSW].[CFNAME] & " " & [CLIENTSW].[CMI] & " " & [CLIENTSW].[CLNAME]

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

I'm developing a form based on the following query that joins a number
of tables on the field [Casenum]. I'm getting the error message that
"The specified field could refer to more than one table listed in the
FROM clause of your SQL statement."

Here's the SQL statement:

SELECT
CLIENTSW.CASENUM,
CLIENTSW.PCODE,
CLIENTSW.DOPEN,
CLIENTSW.FUNDSNUM,
CLIENTSW.DCLOSED,
CLIENTSW.RCLOSED,
CLIENTSW.IMPACT,
CLIENTSW.PHELPED,
CLIENTSW.RECOVERY,
CLIENTSW.MAINBENEFIT,
CLIENTSW.HANDICAPPED,
CLIENTSW.RECOVERYM,
CLIENTSW.AVOID,
CLIENTSW.AVOIDM,
CLIENTSW.GoodStory,
zNotes.Note1,
[clientsw]![cfname] & " " & [clientsw]![cmi] & " " & [clientsw]!
[cLNAME] AS [Client Name],
QOutcomes_for_checkboxes.[Achieved (Won)],
QOutcomes_for_checkboxes.[Partially Achieved (Mixed Result)],
QOutcomes_for_checkboxes.[Did Not Achieve (Lost)],
QOutcomes_for_checkboxes.Underserved,
[Qclosing codes for check boxes].[Counsel and Advice],
[Qclosing codes for check boxes].[Limited Service],
[Qclosing codes for check boxes].[Neg Settle wo Lit],
[Qclosing codes for check boxes].[Neg Settle w Lit],
[Qclosing codes for check boxes].[Admin Decision],
[Qclosing codes for check boxes].[Court Decision Uncontested],
[Qclosing codes for check boxes].[Court Decision contested],
[Qclosing codes for check boxes].[Court Decision Appeal],
[Qclosing codes for check boxes].Other,
[Qclosing codes for check boxes].[Extensive Services],
zMainBenefits.MAINBENEFITB,
zMainBenefits.MAINBENEFITC,
PBI.ATNUM, PBI.PBIHOURS,
SMEMBER.SINITIALS

FROM
(((((CLIENTSW
LEFT JOIN zNotes ON CLIENTSW.CASENUM = zNotes.CASENUM)
LEFT JOIN QOutcomes_for_checkboxes ON CLIENTSW.CASENUM =
QOutcomes_for_checkboxes.CASENUM)
LEFT JOIN [Qclosing codes for check boxes] ON CLIENTSW.CASENUM =
[Qclosing codes for check boxes].CASENUM)
LEFT JOIN zMainBenefits ON CLIENTSW.CASENUM = zMainBenefits.CASENUM)
LEFT JOIN PBI ON CLIENTSW.CASENUM = PBI.CASENUM)
INNER JOIN SMEMBER ON CLIENTSW.SNUM = SMEMBER.SNUM;

Any suggestions as to where I might be going wrong, and how I can fix
this? Thanks.- Hide quoted text -

- Show quoted text -

Thanks for the suggestion, but it does not appear to solve the
problem.

 

[John W.]

I'm developing a form based on the following query that joins a number
of tables on the field [Casenum]. I'm getting the error message that
"The specified field could refer to more than one table listed in the
FROM clause of your SQL statement."

Try replacing ! with . in

[clientsw]![cfname] & " " & [clientsw]![cmi] & " " & [clientsw]!
[cLNAME] AS [Client Name],

The ! delimiter is for form objects (or other types of objects); you can
sometimes get away with it in SQL but the correct table.field specification
uses a period as a delimiter.

             John W. Vinson [MVP]

Thanks for the suggestion, but it does not appear to solve the
problem.

 

[John W. Vinson]

I'm developing a form based on the following query that joins a number
of tables on the field [Casenum]. I'm getting the error message that
"The specified field could refer to more than one table listed in the
FROM clause of your SQL statement."

Try replacing ! with . in

[clientsw]![cfname] & " " & [clientsw]![cmi] & " " & [clientsw]!
[cLNAME] AS [Client Name],

The ! delimiter is for form objects (or other types of objects); you can
sometimes get away with it in SQL but the correct table.field specification
uses a period as a delimiter.

             John W. Vinson [MVP]

Thanks for the suggestion, but it does not appear to solve the
problem.

Are any of the JOINs joining to stored queries instead of tables? If so, can
you open each of these tables independently? The error might be within one of
the subsidiary queries rather than the main one.

John W. Vinson [MVP]