CODEWITHSUNDEEP
sqlms-accesssyntax-error
Dhoward
Dhoward

Reputation: 1

syntax error in join operation

I am getting a syntax error in join operations: there are 2 different table with the same field names, but different field types. one is a autonumber the other is text field. Here is my SQL. could some one please help. 

SELECT [SPO Data Entry Table].[SPO#], [Standard Brk].*, [Standard SubBrk].*
FROM ([SPO Data Entry Table] INNER JOIN [Standard Brk] ON [SPO Data Entry Table].[SPO#] = CDBL(Standard Brk.[SPO#]) 
INNER JOIN [Standard SubBrk] ON [Standard Brk].[Test #] = [Standard SubBrk].[Unit No]
WHERE [SPO Data Entry Table].[SPO#]=Val([Standard Brk].[SPO#]);

SPO Data Entry Table = autonumber
Standard Brk = text

Upvotes: 0

Views: 4236

Answers (1)

Bob Vale
Bob Vale

Reputation: 18474

You are missing the table identifier around Standard Brk in your CDBL

you have CDBL(Standard Brk.[SPO#]) it should be CDBL([Standard Brk].[SPO#])

also you haven't close the opening bracket straight after the from I'm guessing you need to add it straight after the closing bracket of CDBL([Standard Brk].[SPO#])

EDIT -=-=-= Reading the code further, drop the where clause too as it is the same as the inner join clause

Giving you

SELECT [SPO Data Entry Table].[SPO#], [Standard Brk].*, [Standard SubBrk].*
FROM ([SPO Data Entry Table] INNER JOIN [Standard Brk] ON [SPO Data Entry Table].[SPO#] = CDBL([Standard Brk.[SPO#]])) INNER JOIN [Standard SubBrk] ON [Standard Brk].[Test #] = [Standard SubBrk].[Unit No]

Upvotes: 2

Related Questions