|
|
|
date: Thu, 02 Oct 2008 09:38:16 GMT,
group: microsoft.public.access.queries
back
Syntax error missing operator
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
date: Thu, 02 Oct 2008 09:38:16 GMT
author: honisoitquimalypense u46629@uwe
Re: Syntax error missing operator
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 wrote:
> 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
>
date: Thu, 02 Oct 2008 07:41:10 -0400
author: John Spencer
Re: Syntax error missing operator
On Thu, 02 Oct 2008 09:38:16 GMT, "honisoitquimalypense" <u46629@uwe> wrote:
>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.
--
John W. Vinson [MVP]
date: Thu, 02 Oct 2008 13:37:53 -0600
author: John W. Vinson
|
|
