Inner Join In VBA

  • Thread starter Thread starter KostasP via AccessMonster.com
  • Start date Start date
K

KostasP via AccessMonster.com

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