vba sql query

Hello I posted this in the db section of the forums with no help so I figured I'd try here... I'm writing some vba code that accesses a db. I keep receiving the error posted below. The query works fine with two tables and a where but when I try a join (eventually I will need to add a third table). The query is posted below thanks for the help in advance.

Attachment 94221

Code:

---

str = "SELECT CWIPH.CUST_NO, CCSMS.CUST_NO " _
    & "FROM CWIPH " _
    & "JOIN CCSMS ON CWIPH.CUST_NO=CCSMS.CUST_NO "

---

JO

Not sure where you're writing the code, but when I use Excel VBA, here's what I get:

Code:

---

strSQL = "SELECT * FROM tblNames INNER JOIN tblSals ON tblNames.name = tblSals.name"    'works
    strSQL = "SELECT * FROM tblNames JOIN tblSals ON tblNames.name = tblSals.name"          'doesn't work

---

Have you tried using "INNER JOIN" instead?

Thanks the reply. I updated my query and am now receiving the error below

Attachment 94223

Code:

---

str = "SELECT CWIPH.CUST_NO, CCSMS.CUST_NO " _
    & "FROM (CWIPH INNER JOIN CCSMS ON CWIPH.CUST_NO = CCSMS.CUST_NO) "

---

Haven't seen that before, but check this out:

http://support.microsoft.com/kb/964644

does the database you are accessing support joins?

Have you tried the suggestion I made in your other thread?

Quote:

---

Originally Posted by si_the_geek

OK, well try the really simplified version:

Code:

---

  str = "SELECT CCSMS.CUST_NO " _
    & "FROM CSPCL, CCSMS " _
    & "WHERE CCSMS.CUST_NO = CSPCL.CUST_NO "

---

---