sql Inner Join problem

Message Topic Search Topic Options Post Reply Create New Topic Printable Version Translate Topic

   I'm trying to execute the following sql:
sql = "UPDATE BRSched INNER JOIN (Invoice INNER JOIN BrProp ON Invoice.Propno = BrProp.PropNo) ON (BRSched.Version = BrProp.schedno) AND (BRSched.Propno = BrProp.fmPropNo) SET BRSched.Invno = Invoice.Invno WHERE BRSched.Invno = 0 AND BrProp.PropNo ='" & radio & "' AND Invoice.PropType='Broadacre'" 
but it is being very unhelpful and giving me the following error:

Microsoft OLE DB Provider for SQL Server (0x80040E14)
Incorrect syntax near the keyword 'INNER'.

this query is derived from a working access 2003 query, as follows:

"UPDATE BRSched INNER JOIN (Invoice INNER JOIN BrProp ON Invoice.Propno = BrProp.PropNo) ON (BRSched.Version = BrProp.schedno) AND (BRSched.Propno = BrProp.fmPropNo) SET BRSched.Invno = [invoice].[invno] WHERE (((BRSched.Invno)=0) AND ((BrProp.PropNo)=[forms]![CLIENT].[vpno]) AND ((Invoice.PropType)='Broadacre'));"

I've converted the access references (ie the [forms]![CLIENT].[vpno] type stuff) to the appropriate references, but as you can see I haven't touched first part. Is there anything i should know about asp getting narky about 'inner join'?