>Ok, this is not a VFP question, but I am not getting any hits on this in the VB forum. I'm working on an application to query data on two-related, but not normalized tables in an Access database using DA0. The multi-field foreign key to the parent key exists, but the data-types and length do not match.
>
>Parent table Primary key:
>Part C(240)
>Line C(240)
>
>Child table foreign key:
>Part-Number C(255)
>Line N()-Double
>
>The code I am using to join in VB
>
>sSQLStatement = "SELECT Production.Line,Production.[Part-Number],T12Curr.Steel_Type FROM t12curr " + _
>"LEFT JOIN Production ON TRIM(t12curr.part) = TRIM(Production.[Part-Number]" + _
>"AND ON val(t12curr.line) = Production.Line ORDER BY Production.[Part-Number],Production.Line"
>Set qryGetData = odb.CreateQueryDef("", sSQLStatement)
>Set recResults = qryGetData.OpenRecordset(dbOpenSnapshot)
>
>
>I keep getting a syntax error on the "set qryGetData = ..." statement. I double checked the syntax on the LEFT JOIN in the on-line help and can not see anything that is coded wrong. Can anybody help shed some light on the nature of the error?
>
>PS- Keeping this legacy system "limping" along until it is replaced by an ERP system has just reinforced my theory that VFP is heads and shoulders above VB combined with Access.
>
>Thanks,
>Dave
Dave,
I don't think SQL would change in ADO. If so correct syntax is :

sSQLStatement = "SELECT Production.Line,Production.[Part-Number],T12Curr.Steel_Type FROM t12curr " + _
"LEFT JOIN Production ON TRIM(t12curr.part) = TRIM(Production.[Part-Number]" + _
"where val(t12curr.line) = Production.Line ORDER BY Production.[Part-Number],Production.Line"

Cetin