Way OT excel help

[lifeson]

Way off topic but someone may be able to help
Need abit of excel help if we have any gurus out there

I am using vba in excel to get data from an Acccess database.
I can get it to run querrys on one table but when I join two related tables together I get an error
This is the code:

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 message says join method not suppoted.

Anybody know where I have gone wrong (statring the project is where I really went wrong ) or the correct syntax for joining two tables in a VBA querry?

 

[LordoftheTeapot]

Way off topic but someone may be able to help
Need abit of excel help if we have any gurus out there

I am using vba in excel to get data from an Acccess database.
I can get it to run querrys on one table but when I join two related tables together I get an error
This is the code:

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 message says join method not suppoted.

Anybody know where I have gone wrong (statring the project is where I really went wrong ) or the correct syntax for joining two tables in a VBA querry?

No idea.

 

[bonj2]

Way off topic but someone may be able to help
Need abit of excel help if we have any gurus out there

I am using vba in excel to get data from an Acccess database.
I can get it to run querrys on one table but when I join two related tables together I get an error
This is the code:

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 message says join method not suppoted.

Anybody know where I have gone wrong (statring the project is where I really went wrong ) or the correct syntax for joining two tables in a VBA querry?

Try

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

spot the difference?

 

[Abitrary]

i did that today, left a space out making a longer string

 

[Aperitif]

Took me ages to spot bonj'sdifference...

Flippin' heckles..!

 

[yenrod]

When I left school I learnt about Excel.

It puzzled me to distraction BUT now I'm fairly conversant with it but that went by my head like a jet....

Hope you solve the prob'. !

 

[andygates]

Um, Excel does support the JOIN method?

 

[Crackle]

It's reporting the error though and I'm guessing Lifeson didn't type that but used Excels Macros/codebuilder to do it (could well be wrong though, wouldn't be the first time).

Is it possibly to do with attempting to join two different types of field ie one is text (but only contains numbers) and the other a number or you've got the join the wrong way around and it needs to be an outer join (one to many, many to one) or it isn't a unique field in one of the tables. They're my number one table join culprits. Don't ask me about the VBA though.

 

[Abitrary]

Bonj has already corrected it. It's a typo in the sql being sent to the database meaning that some words have got mashed together.

 

[Abitrary]

(think there's a bracket missing at the end there as well)

 

[bonj2]

Um, Excel does support the JOIN method?

he's calling through to access:

Way off topic but someone may be able to help
Need abit of excel help if we have any gurus out there

I am using vba in excel to get data from an Acccess database.
I can get it to run querrys on one table but when I join two related tables together I get an error

 

[Sh4rkyBloke]

Access???

Get yourself a proper database.

 

[lifeson]

Solved

Apparently I didnt neeed the OWNERACCESS option in the SQL
this solved it

"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)=" & leadID & ") " & _
"AND ((TblDiscountsApplied.QuoteID)='" & quoteID & "') " & _
"AND ((TblDiscountsApplied.ResourceType)='" & resourceID & "'));"