Inner Join In VBA

[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

 

[Allen Browne]

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

 

[KostasP via AccessMonster.com]

Many Thanks Allen Browne!
Did work!