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

Have been here a while

• 2

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

luzz

Registered User.

• 3

  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

Registered User.

• 4

  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

• 5

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

Registered User.

• 6

  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?

• 7

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

Last edited: Dec 6, 2017

luzz

Registered User.

• 8

  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!

• 9

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

luzz

Registered User.

• 10

  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.