1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Way OT excel help

Discussion in 'CycleChat Cafe' started by lifeson, 1 Feb 2008.

  1. lifeson

    lifeson New Member

    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?
     
  2. Lord of the Teapot

    Lord of the Teapot New Member

    No idea. ;)
     
  3. bonj2

    bonj2 Guest

    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?
     
  4. Abitrary

    Abitrary New Member

    i did that today, left a space out making a longer string
     
  5. Took me ages to spot bonj'sdifference...

    Flippin' heckles..! :biggrin:
     
  6. yenrod

    yenrod Guest

    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'. !
     
  7. andygates

    andygates New Member

    Um, Excel does support the JOIN method?
     
  8. Crackle

    Crackle Pah Staff Member

    Location:
    Wirral
    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.
     
  9. Abitrary

    Abitrary New Member

    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.
     
  10. Abitrary

    Abitrary New Member

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

    bonj2 Guest

    he's calling through to access:

     
  12. Sh4rkyBloke

    Sh4rkyBloke Jaffa Cake monster

    Location:
    Manchester, UK
    Access???

    Get yourself a proper database. :wacko:;)
     
  13. OP
    OP
    lifeson

    lifeson New Member

    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 & "'));"