>>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

Cetin,
Thanks for the response. That worked for me.