Syntax error missing operator

[honisoitquimalypense]

Hi,

i have a database holding details of tasks within a project, i have a main
form containing some information, unique task ID number and overall job
description and a subform with various yes/no fields relating to the main
task, with the data from each form being held in separate tables linked by a
primary key of task ID number.

I'm trying to produce a query to join up these two tables and produce a
report for only one record at a time. I've have created the below SQL to
achieve my goal, however, i keep getting a "syntax error missing operator
[Task list].[Task number] = Forms!main database test!Task number! AND TIF.
Task Number = Forms!Main database test!TIF subform.Form!Task number" dialog
box.

I'm new to SQL so I'm not sure where I'm going wrong? can anyone help?

SELECT [Task list].[Task number] AS [Task list_Task number], [Task list].
Description, TIF.[Task Definition Document], TIF.[Cost & Time Scale Estimate],
TIF.[Project or Task Plan], TIF.[Quality Plan], TIF.[Engineering Change], TIF.
[Functional Design Specification], TIF.[Design Change Specification], TIF.
[Implementation & Test Specification], TIF.[Commissioning Procedures], TIF.
Other, TIF.[Please Specify], TIF.[Task Number] AS [TIF_Task Number], TIF.
[Options/Concepts Identified], TIF.[End User & PAE Consulted], TIF.[Task
Objectives Identified], TIF.[Task Defined and Scoped], TIF.[Requirements for
Scope/Delivery Stated], TIF.[Task Database Updated]
FROM [Task list] INNER JOIN TIF ON [Task list].[Task number]=TIF.[Task Number]
WHERE [Task list].[Task number] = Forms!main database test!Task number! AND
TIF.Task Number = Forms!Main database test!TIF subform.Form!Task number

 

[John Spencer]

SPACES are your enemy. Surround your enemy with square brackets. When you have
field names, table names, or object names with spaces you must surround the
names with brackets.

WHERE [Task list].[Task number] = Forms![main database test]![Task number]
AND TIF.Task Number = Forms![Main database test]![TIF subform].Form![Task number]

By the way, I'm not sure you can refer to a subform control in a query. The
expression service may not handle that.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[honisoitquimalypense via AccessMonster.com]

thanks john. i've now surrounded everything with the brackets, but i get
exactally the same error message except with brackets this time. is there
anything else i'm getting wrong regarding the syntax?

SPACES are your enemy. Surround your enemy with square brackets. When you have
field names, table names, or object names with spaces you must surround the
names with brackets.

WHERE [Task list].[Task number] = Forms![main database test]![Task number]
AND TIF.Task Number = Forms![Main database test]![TIF subform].Form![Task number]

By the way, I'm not sure you can refer to a subform control in a query. The
expression service may not handle that.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi,

[quoted text clipped - 25 lines]

WHERE [Task list].[Task number] = Forms!main database test!Task number! AND
TIF.Task Number = Forms!Main database test!TIF subform.Form!Task number

 

[John W. Vinson]

Hi,

i have a database holding details of tasks within a project, i have a main
form containing some information, unique task ID number and overall job
description and a subform with various yes/no fields relating to the main
task, with the data from each form being held in separate tables linked by a
primary key of task ID number.

I'm trying to produce a query to join up these two tables and produce a
report for only one record at a time. I've have created the below SQL to
achieve my goal, however, i keep getting a "syntax error missing operator
[Task list].[Task number] = Forms!main database test!Task number! AND TIF.
Task Number = Forms!Main database test!TIF subform.Form!Task number" dialog
box.

I'm new to SQL so I'm not sure where I'm going wrong? can anyone help?

SELECT [Task list].[Task number] AS [Task list_Task number], [Task list].
Description, TIF.[Task Definition Document], TIF.[Cost & Time Scale Estimate],
TIF.[Project or Task Plan], TIF.[Quality Plan], TIF.[Engineering Change], TIF.
[Functional Design Specification], TIF.[Design Change Specification], TIF.
[Implementation & Test Specification], TIF.[Commissioning Procedures], TIF.
Other, TIF.[Please Specify], TIF.[Task Number] AS [TIF_Task Number], TIF.
[Options/Concepts Identified], TIF.[End User & PAE Consulted], TIF.[Task
Objectives Identified], TIF.[Task Defined and Scoped], TIF.[Requirements for
Scope/Delivery Stated], TIF.[Task Database Updated]
FROM [Task list] INNER JOIN TIF ON [Task list].[Task number]=TIF.[Task Number]
WHERE [Task list].[Task number] = Forms!main database test!Task number! AND
TIF.Task Number = Forms!Main database test!TIF subform.Form!Task number

After you get the brackets as John suggests, and get rid of the trailing
exclamation point on

WHERE [Task list].[Task number] = Forms!main database test!Task number! AND

then consider whether your criteria are correct. You're already joining [Task
List] to [TIF] on Task Number, so you certainly do not need the criterion on
both fields; I'd just leave the one on the parent table. If the form control
is NULL you will get this error as well.

 

[honisoitquimalypense via AccessMonster.com]

Thank you guys. i've removed the AND part and the query now works.

Hi,

[quoted text clipped - 25 lines]

WHERE [Task list].[Task number] = Forms!main database test!Task number! AND
TIF.Task Number = Forms!Main database test!TIF subform.Form!Task number

After you get the brackets as John suggests, and get rid of the trailing
exclamation point on

WHERE [Task list].[Task number] = Forms!main database test!Task number! AND

then consider whether your criteria are correct. You're already joining [Task
List] to [TIF] on Task Number, so you certainly do not need the criterion on
both fields; I'd just leave the one on the parent table. If the form control
is NULL you will get this error as well.