Error with Inner Joins on 3 tables

[pbassett]

I have 3 tables - the first 2 tables are linked by PIW and Ref, and the 1st and 3rd by PIW. The field Ref may be Null; hence the nz() addition in the following erroneous SQL:

SELECT [A].*, .*, [C].[status]
FROM [A]
INNER JOIN ON [A].[PIW]=.[PIW] AND
nz([A].[Ref],"")=nz(.[Ref],"")
INNER JOIN [C] ON [A].[PIW]=[C].[PIW]

I get the message "Syntax Error - Missing Operator".

I do know this 2-table query works:

SELECT [A].*, .*
FROM [A]
INNER JOIN ON [A].[PIW]=.[PIW] AND
nz([A].[Ref],"")=nz(.[Ref],"")

Any suggestions to get the first query to work?

Thanks,
Pete

 

[Norie]

Are you just using SQL or working with the query designer?

 

[pbassett]

I have to use SQL only since the working INNER JOIN with the nz() has resulted in the Design Tool no longer being accessible.

 

[Norie]

What do you wish to do when Ref is null?

I don't quite understand why you are using Nz.

 

[pbassett]

I could not get matches for a PIW and Ref if Ref was Null, since you can't Null fields to match in a SELECT. So by forcing Ref to " " via the nz() I can at least get matches. For example, I may have a PIW of 19010 and a Null Ref in the 2 tables. without nz() I wouldn't link them since the Null Ref fields would not match, as Null doesn't equal Null in SQL.

 

[Norie]

I'm sure there is another way to do it but if it works it works.

BTW

The 'syntax error is being caused by a missing bracket

SELECT [A].*, .*, [C].[status]
FROM [A]
INNER JOIN ON [A].[PIW]=.[PIW] AND
(nz([A].[Ref],"")=nz(.[Ref],"")
INNER JOIN [C] ON [A].[PIW]=[C].[PIW]

 

[pbassett]

I got it to work putting parentheses after the FROM and after the first INNER JOIN, like this:

SELECT [A].*, .*, [C].[status]
FROM ([A]
INNER JOIN ON [A].[PIW]=.[PIW] AND
nz([A].[Ref],"")=nz(.[Ref],""))
INNER JOIN [C] ON [A].[PIW]=[C].[PIW]