lifeson
02-01-2008, 07:17 AM
I am getting a problem when trying to join 2 related access tables in an excel vba query
src = "SELECT TblDiscountsApplied.LeadNumber, " & _
"TblDiscountsApplied.QuoteID, " & _
"TblDiscountsApplied.ResourceType, " & _
"TblDiscountsApplied.DiscountCodeID, " & _
"TblDiscount.DiscountDescription, " & _
"TblDiscountsApplied.Value " & _
"FROM TblDiscountsApplied " & _
"INNER JOIN TblDiscount " & _
"ON TblDiscountsApplied.DiscountCodeID = TblDiscount.DiscountCodeID" & _
"WHERE (((TblDiscountsApplied.LeadNumber) = 30520827) And ((TblDiscountsApplied.quoteID) = 'A'))" & _
"WITH OWNERACCESS OPTION;"
The error says join expression not supported.
The two tables linked are TblDiscount which holds the discount code and discount description and TblDiscountsApplied which holds the discounts applied as a code.
I have joined the two tables as a query in access an it worked fine so I copied the SQL into VBA and now I get the error
Obviously I have the src wrong
Can anyone point me in the right direction please?
src = "SELECT TblDiscountsApplied.LeadNumber, " & _
"TblDiscountsApplied.QuoteID, " & _
"TblDiscountsApplied.ResourceType, " & _
"TblDiscountsApplied.DiscountCodeID, " & _
"TblDiscount.DiscountDescription, " & _
"TblDiscountsApplied.Value " & _
"FROM TblDiscountsApplied " & _
"INNER JOIN TblDiscount " & _
"ON TblDiscountsApplied.DiscountCodeID = TblDiscount.DiscountCodeID" & _
"WHERE (((TblDiscountsApplied.LeadNumber) = 30520827) And ((TblDiscountsApplied.quoteID) = 'A'))" & _
"WITH OWNERACCESS OPTION;"
The error says join expression not supported.
The two tables linked are TblDiscount which holds the discount code and discount description and TblDiscountsApplied which holds the discounts applied as a code.
I have joined the two tables as a query in access an it worked fine so I copied the SQL into VBA and now I get the error
Obviously I have the src wrong
Can anyone point me in the right direction please?