Inner Join In VBA

  • Thread starter KostasP via AccessMonster.com
  • 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
 
A

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
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top