Inner Join In VBA

Discussion in 'Microsoft Access VBA Modules' started by KostasP via AccessMonster.com, Nov 22, 2007.

  1. Hi Everyone!
    This is my first post and i need your help,
    I have two tables with a one-to-many relationship and i need to make a query
    in VBA using SQL (INNER JOIN) statement, in order to get my preferred result.

    Here is the example of my recent code that doesn't work by the way:
    The related Tables are KAM and KAM Port.
    sr = "SELECT Count(CFR) AS [URV],"
    sr = sr + "Avg(KAM.Loa) AS [MLREF]"
    sr = sr + "FROM KAM Port INNER JOIN KAM ON KAM Port.Port Name]=" + "
    KAM.[Port Name]"
    sr = sr + "AND (KAM Port.[Port Code]=" + "KAM.[Port Code] WHERE "
    sr = sr + "ComYear<=" + Str(nb) + " AND EventEndDate>=" + Str(nb) +
    "9999"
    sr = sr + " AND " + sport
    sr = sr + " AND (KAM.[Ton Ref])=" + "0"

    What am i doing wrong?
    Thanks in advance!
    KostaP

    --
    Message posted via AccessMonster.com
    http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200711/1
     
    KostasP via AccessMonster.com, Nov 22, 2007
    #1
    1. Advertisements

  2. KostasP via AccessMonster.com

    Allen Browne Guest

    Your 2nd table name contains a space, so you must use square brackets around
    it, i.e.:
    [KAM Port]

    If you do get stuck, you can use the graphic query designer to design the
    query you want, using any literal criteia that can act as samples. Then
    switch the query to SQL View (View menu, in query design) to see what your
    query statement should look like.

    That's what I do, and then use this utility to create the SQL string in VBA
    code:
    Copy SQL statement from query to VBA
    at:
    http://allenbrowne.com/ser-71.html

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "KostasP via AccessMonster.com" <u39264@uwe> wrote in message
    news:7b9622fb527f0@uwe...
    > Hi Everyone!
    > This is my first post and i need your help,
    > I have two tables with a one-to-many relationship and i need to make a
    > query
    > in VBA using SQL (INNER JOIN) statement, in order to get my preferred
    > result.
    >
    > Here is the example of my recent code that doesn't work by the way:
    > The related Tables are KAM and KAM Port.
    > sr = "SELECT Count(CFR) AS [URV],"
    > sr = sr + "Avg(KAM.Loa) AS [MLREF]"
    > sr = sr + "FROM KAM Port INNER JOIN KAM ON KAM Port.Port Name]=" +
    > "
    > KAM.[Port Name]"
    > sr = sr + "AND (KAM Port.[Port Code]=" + "KAM.[Port Code] WHERE "
    > sr = sr + "ComYear<=" + Str(nb) + " AND EventEndDate>=" + Str(nb) +
    > "9999"
    > sr = sr + " AND " + sport
    > sr = sr + " AND (KAM.[Ton Ref])=" + "0"
    >
    > What am i doing wrong?
    > Thanks in advance!
    > KostaP
     
    Allen Browne, Nov 22, 2007
    #2
    1. Advertisements

  3. KostasP via AccessMonster.com, Nov 27, 2007
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    Left Join acting like inner join???

    Guest, Jun 8, 2005, in forum: Microsoft Access VBA Modules
    Replies:
    7
    Views:
    566
    Guest
    Jun 10, 2005
  2. Guest

    Update with Inner Join

    Guest, Nov 9, 2005, in forum: Microsoft Access VBA Modules
    Replies:
    6
    Views:
    492
    Michel Walsh
    Dec 6, 2005
  3. gci1000

    VBA problem with inner DB loop

    gci1000, Jun 24, 2008, in forum: Microsoft Access VBA Modules
    Replies:
    6
    Views:
    238
    Dirk Goldgar
    Jun 24, 2008
  4. stickandrock

    Dlookup with Inner Join

    stickandrock, Mar 7, 2009, in forum: Microsoft Access VBA Modules
    Replies:
    2
    Views:
    1,727
    stickandrock
    Mar 7, 2009
  5. Hugh self taught

    SQL JOIN for Unmatched records & JOIN fields from another table

    Hugh self taught, Jan 4, 2010, in forum: Microsoft Access VBA Modules
    Replies:
    4
    Views:
    2,601
    Hugh self taught
    Jan 7, 2010
Loading...

Share This Page