Syntax Error (missing operator) in query expression (1 Viewer)

luzz · Dec 6, 2017

Hi all, I get the error when i run my query and i have check that i do not miss out any open or close bracket. How can i fix this?

Code:

SELECT FabricPOqry*
From FabricPOqry LEFT JOIN MainTable ON (FabricPOqry.StyleNO = MainTable.StyleNo) AND (FabricPOqry.[GL Lot] = MainTable.GLA) AND (FabricPOqry.PO = MainTable.GLGPO) AND (FabricPOqry.Line = MainTable.Line) AND (FabricPOqry.[Our Qty] = MainTable.Lbs) AND (FabricPOqry.[Supplier Qty]  = MainTable.Yds) AND 
(FabricPOqry.GSMBeforeWash = MainTable.FinishedGoods) AND (FabricPOqry.GMSPerSqYD = MainTable.GSMPerSqYd)
WHERE (((FabricPOqry.StyleNO) is Null) AND ((FabricPOqry.[GL Lot]) is  Null) AND ((FabricPOqry.PO) is Null) AND ((FabricPOqry.Line) is Null) AND ((FabricPOqry.[Our Qty]) is Null) AND ((FabricPOqry.[Supplier Qty]) is Null) AND ((FabricPOqry.GSMBeforeWash) is Null) AND ((FabricPOqry.GMSPerSqYD) is Null));

JHB · Dec 6, 2017

This one is the problem FabricPOqry* you're missing the dot between the table name and the star!
Correct is FabricPOqry.*

luzz · Dec 6, 2017

JHB said:
This one is the problem FabricPOqry* you're missing the dot between the table name and the star!
Correct is FabricPOqry.*

Thanks! After i have added in the . and run again. It prompt me another error"Syntax error (missing operator) in query expression".

luzz · Dec 6, 2017

JHB said:
This one is the problem FabricPOqry* you're missing the dot between the table name and the star!
Correct is FabricPOqry.*

Thanks! I have fixed the error. However, when i run the query, no records is shown on the query

The_Doc_Man · Dec 6, 2017

That query has to have a LOT of things going for it in order to return any records.

FIRST, you need to find eight of your FabricPOqry object (whatever it is) with nulls. But SECOND, you need to find records where eight OTHER fields exactly match some record in the MainTable object (which I assume is a table). That is 16 exacting criteria.

In order to partly debug this, I might run the query without the WHERE clause to see if your problem is an over-restrictive JOIN. The WHERE filters will return nothing in either of two cases, you see: No records matched the WHERE clause or no records were found for the eight-way JOIN.

If you do this without the WHERE clause and STILL get nothing, your problem is that the JOIN over-specifies the selection criteria.

luzz · Dec 6, 2017

The_Doc_Man said:
That query has to have a LOT of things going for it in order to return any records.

FIRST, you need to find eight of your FabricPOqry object (whatever it is) with nulls. But SECOND, you need to find records where eight OTHER fields exactly match some record in the MainTable object (which I assume is a table). That is 16 exacting criteria.

In order to partly debug this, I might run the query without the WHERE clause to see if your problem is an over-restrictive JOIN. The WHERE filters will return nothing in either of two cases, you see: No records matched the WHERE clause or no records were found for the eight-way JOIN.

If you do this without the WHERE clause and STILL get nothing, your problem is that the JOIN over-specifies the selection criteria.

The query returned me results after i remove the where clause. How do i move on from here?

isladogs · Dec 6, 2017

Try adding them back one at a time so you can see which criterion or criteria cause your issue

Also consider whether any of these could be done using OR rather than AND

luzz · Dec 6, 2017

ridders said:
Ty adding them back one at a time so you can see which criterion or criteria cause your issue

Also consider whether any of these could be done using OR rather than AND

Thank you! I have solved it!

isladogs · Dec 6, 2017

For the benefit of others who may view this thread in future, please explain what the solution was

luzz · Dec 7, 2017

ridders said:
For the benefit of others who may view this thread in future, please explain what the solution was

The purpose of me doing this query is to build an unmatched query based on multiple fields. After researching, i found out that i can actually use the unmatched wizard in Access to help me do that. If i want to have other join criteria i can simply type in my own criteria in the unmatched query SQL.

Syntax Error (missing operator) in query expression (1 Viewer)

luzz

Registered User.

JHB

Have been here a while

luzz

Registered User.

luzz

Registered User.

The_Doc_Man

Immoderate Moderator

luzz

Registered User.

isladogs

MVP / VIP

luzz

Registered User.

isladogs

MVP / VIP

luzz

Registered User.

Similar threads

Users who are viewing this thread