Syntax error (missing operator) in query expression ‘tblDUES.’.

M

Mike Da Commie

I have a query “qryDUES_DATA†that combines two tables (or queries) with a
simple [Enter date like 200812] which limits the returned records from the
files tagged as 200812. In the example the two tables are named “tblDUES†/
“qryDUES_RAW†and a subtable “tblCOMMENT†/ “qryDUES_COMMENTâ€.

It is a “select query†built by the wizard or if I build it in the query
design window and I get the same results. As a query it works fine.

I tried it by doing it on the two tables and also by trying it on two
queries with the same table data.

When I open a Form or Report in design view; based on the query, and then
try to view the Form or Report, I get an error message:

Syntax error (missing operator) in query expression ‘tblDUES.’.

The same problem happens with other queries also.

When I go back to the query, it has changed from a “select query†to an “SQL
Union queryâ€, and I can not open it in query design view with same error
message, only in SQL view. In not well versed in SQL code.

Here is the SQL code before the error:

SELECT qryDUES_RAW.key, qryDUES_RAW.REPORTMONTH, qryDUES_RAW.ID,
qryDUES_RAW.SIN, qryDUES_RAW.EMPCAT, qryDUES_RAW.UNION, qryDUES_RAW.MEMBER,
qryDUES_RAW.DUESMONTH, qryDUES_RAW.RECEIVED, qryDUES_RAW.EXPECTED,
qryDUES_RAW.SETTLED, qryDUES_RAW.[IN], qryDUES_RAW.OUT, qryDUES_RAW.YTD,
qryDUES_RAW.REBATE1, qryDUES_RAW.REBATE2, qryDUES_RAW.WAIVER,
qryDUES_RAW.REBATE3, qryDUES_COMMENT.KEY, qryDUES_COMMENT.PARENTKEY,
qryDUES_COMMENT.COMMENT
FROM qryDUES_RAW LEFT JOIN qryDUES_COMMENT ON qryDUES_RAW.key =
qryDUES_COMMENT.PARENTKEY
WHERE (((qryDUES_RAW.REPORTMONTH)=[Enter month like 200812]));


Here is the SQL code after the error:

SELECT qryDUES_RAW.key AS qryDUES_RAW_key, qryDUES_RAW.REPORTMONTH,
qryDUES_RAW.ID, qryDUES_RAW.SIN, qryDUES_RAW.EMPCAT, qryDUES_RAW.UNION ,
qryDUES_RAW.MEMBER, qryDUES_RAW.DUESMONTH, qryDUES_RAW.RECEIVED,
qryDUES_RAW.EXPECTED, qryDUES_RAW.SETTLED, qryDUES_RAW.[IN], qryDUES_RAW.OUT,
qryDUES_RAW.YTD, qryDUES_RAW.REBATE1, qryDUES_RAW.REBATE2,
qryDUES_RAW.WAIVER, qryDUES_RAW.REBATE3, qryDUES_COMMENT.KEY AS
qryDUES_COMMENT_KEY, qryDUES_COMMENT.PARENTKEY, qryDUES_COMMENT.COMMENT FROM
qryDUES_RAW LEFT JOIN qryDUES_COMMENT ON
qryDUES_RAW.key=qryDUES_COMMENT.PARENTKEY
ORDER BY qryDUES_RAW.REPORTMONTH, qryDUES_RAW.DUESMONTH;

Any idea what I’m doing wrong?

There is one table linked to another database, but the database is a single
mdb, not split into client / server. It is in Access 2003.

It’s got me stumped and I’ve been working on for a couple days and can’t
figure it out. Should I try being more explicit and use something like
[qryDUES_RAW]![key] when constructing the query?. Is it an Access 2003 bug,
the mdb was designed in Access 2000.

Thanks

Mike in Canada
 
J

John Spencer

I would suspect that the problem might be in qryDues_Raw which you did not
show us. Often if there is an error in a calculated field it won't show up
until you try to search or sort on the calculated field that is generating an
error. Sometimes you are using a function that cannot handle nulls and that
generates an error that is not evident until the subsequent query applies
criteria against the nested query.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I have a query “qryDUES_DATA†that combines two tables (or queries) with a
simple [Enter date like 200812] which limits the returned records from the
files tagged as 200812. In the example the two tables are named “tblDUES†/
“qryDUES_RAW†and a subtable “tblCOMMENT†/ “qryDUES_COMMENTâ€.

It is a “select query†built by the wizard or if I build it in the query
design window and I get the same results. As a query it works fine.

I tried it by doing it on the two tables and also by trying it on two
queries with the same table data.

When I open a Form or Report in design view; based on the query, and then
try to view the Form or Report, I get an error message:

Syntax error (missing operator) in query expression ‘tblDUES.’.

The same problem happens with other queries also.

When I go back to the query, it has changed from a “select query†to an “SQL
Union queryâ€, and I can not open it in query design view with same error
message, only in SQL view. In not well versed in SQL code.

Here is the SQL code before the error:

SELECT qryDUES_RAW.key, qryDUES_RAW.REPORTMONTH, qryDUES_RAW.ID,
qryDUES_RAW.SIN, qryDUES_RAW.EMPCAT, qryDUES_RAW.UNION, qryDUES_RAW.MEMBER,
qryDUES_RAW.DUESMONTH, qryDUES_RAW.RECEIVED, qryDUES_RAW.EXPECTED,
qryDUES_RAW.SETTLED, qryDUES_RAW.[IN], qryDUES_RAW.OUT, qryDUES_RAW.YTD,
qryDUES_RAW.REBATE1, qryDUES_RAW.REBATE2, qryDUES_RAW.WAIVER,
qryDUES_RAW.REBATE3, qryDUES_COMMENT.KEY, qryDUES_COMMENT.PARENTKEY,
qryDUES_COMMENT.COMMENT
FROM qryDUES_RAW LEFT JOIN qryDUES_COMMENT ON qryDUES_RAW.key =
qryDUES_COMMENT.PARENTKEY
WHERE (((qryDUES_RAW.REPORTMONTH)=[Enter month like 200812]));


Here is the SQL code after the error:

SELECT qryDUES_RAW.key AS qryDUES_RAW_key, qryDUES_RAW.REPORTMONTH,
qryDUES_RAW.ID, qryDUES_RAW.SIN, qryDUES_RAW.EMPCAT, qryDUES_RAW.UNION ,
qryDUES_RAW.MEMBER, qryDUES_RAW.DUESMONTH, qryDUES_RAW.RECEIVED,
qryDUES_RAW.EXPECTED, qryDUES_RAW.SETTLED, qryDUES_RAW.[IN], qryDUES_RAW.OUT,
qryDUES_RAW.YTD, qryDUES_RAW.REBATE1, qryDUES_RAW.REBATE2,
qryDUES_RAW.WAIVER, qryDUES_RAW.REBATE3, qryDUES_COMMENT.KEY AS
qryDUES_COMMENT_KEY, qryDUES_COMMENT.PARENTKEY, qryDUES_COMMENT.COMMENT FROM
qryDUES_RAW LEFT JOIN qryDUES_COMMENT ON
qryDUES_RAW.key=qryDUES_COMMENT.PARENTKEY
ORDER BY qryDUES_RAW.REPORTMONTH, qryDUES_RAW.DUESMONTH;

Any idea what I’m doing wrong?

There is one table linked to another database, but the database is a single
mdb, not split into client / server. It is in Access 2003.

It’s got me stumped and I’ve been working on for a couple days and can’t
figure it out. Should I try being more explicit and use something like
[qryDUES_RAW]![key] when constructing the query?. Is it an Access 2003 bug,
the mdb was designed in Access 2000.

Thanks

Mike in Canada
Click to expand...
 
M

Mike Da Commie

Here is qryDUES_RAW

SELECT tblDUES.key, tblDUES.REPORTMONTH, tblDUES.ID, tblDUES.SIN,
tblDUES.EMPCAT, tblDUES.UNION, tblDUES.MEMBER, tblDUES.DUESMONTH,
tblDUES.RECEIVED, tblDUES.EXPECTED, tblDUES.SETTLED, tblDUES.[IN],
tblDUES.OUT, tblDUES.YTD, tblDUES.REBATE1, tblDUES.REBATE2, tblDUES.WAIVER,
tblDUES.REBATE3
FROM tblDUES;

--
Mike in Canada


John Spencer said:
I would suspect that the problem might be in qryDues_Raw which you did not
show us. Often if there is an error in a calculated field it won't show up
until you try to search or sort on the calculated field that is generating an
error. Sometimes you are using a function that cannot handle nulls and that
generates an error that is not evident until the subsequent query applies
criteria against the nested query.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Click to expand...

<snip>
 
M

Mike Da Commie

Ohhh, looking at that code, could it be I have a field named "tblDUES.UNION"?
Access is confused about the reserved word "union" when not in brackets with
a bang? I can rename the field.
--
Mike in Canada


Mike Da Commie said:
Here is qryDUES_RAW

SELECT tblDUES.key, tblDUES.REPORTMONTH, tblDUES.ID, tblDUES.SIN,
tblDUES.EMPCAT, tblDUES.UNION, tblDUES.MEMBER, tblDUES.DUESMONTH,
tblDUES.RECEIVED, tblDUES.EXPECTED, tblDUES.SETTLED, tblDUES.[IN],
tblDUES.OUT, tblDUES.YTD, tblDUES.REBATE1, tblDUES.REBATE2, tblDUES.WAIVER,
tblDUES.REBATE3
FROM tblDUES;

--
Mike in Canada


John Spencer said:
I would suspect that the problem might be in qryDues_Raw which you did not
show us. Often if there is an error in a calculated field it won't show up
until you try to search or sort on the calculated field that is generating an
error. Sometimes you are using a function that cannot handle nulls and that
generates an error that is not evident until the subsequent query applies
criteria against the nested query.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Click to expand...

<snip>
Click to expand...
 
K

KARL DEWEY

Try changing field names of [UNION] and [IN] to something else as these are
reserved words.

--
Build a little, test a little.


Mike Da Commie said:
Here is qryDUES_RAW

SELECT tblDUES.key, tblDUES.REPORTMONTH, tblDUES.ID, tblDUES.SIN,
tblDUES.EMPCAT, tblDUES.UNION, tblDUES.MEMBER, tblDUES.DUESMONTH,
tblDUES.RECEIVED, tblDUES.EXPECTED, tblDUES.SETTLED, tblDUES.[IN],
tblDUES.OUT, tblDUES.YTD, tblDUES.REBATE1, tblDUES.REBATE2, tblDUES.WAIVER,
tblDUES.REBATE3
FROM tblDUES;

--
Mike in Canada


John Spencer said:
I would suspect that the problem might be in qryDues_Raw which you did not
show us. Often if there is an error in a calculated field it won't show up
until you try to search or sort on the calculated field that is generating an
error. Sometimes you are using a function that cannot handle nulls and that
generates an error that is not evident until the subsequent query applies
criteria against the nested query.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Click to expand...

<snip>
Click to expand...
 
M

Mike Da Commie

Thanks Karl, it worked. Going to watch my names in the future!
--
Mike in Canada


KARL DEWEY said:
Try changing field names of [UNION] and [IN] to something else as these are
reserved words.
Click to expand...
<snip>
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Syntax error (missing operator) in query expression message 1

Top