JOIN query to DBF via VBA

I apologize if this has been asked but i can't find where i'm going wrong here.

I need to query (2) dbf tables AP and VENDOR which contain vendors and payables. I need to get a list of all the payables entered between two specified dates. ap_vendor contains the vendor ID in the AP table and v_vendor contains the vendor ID in the vendor table.

I need to use a join to return the vendor name and the amount which are in separate tables.

This is my query:

SELECT a.ap_invamt, a.ap_invoice, b.v_name 
          FROM AP a JOIN VENDOR b 
          ON a.ap_vendor = b.v_vendor

I need to add a WHERE clause as well but i cant even get this part to run.

Keep getting error: "Syntax error in FROM clause"

Solution

Unlike other SQL dialects, you cannot use just the word JOIN to specify an inner join in Access (JET) SQL. You have to use both keywords: a INNER JOIN b.

Interestingly enough, I just tested it and JET does allow for LEFT JOIN and RIGHT JOIN, without the OUTER keyword.

Change your query to read FROM AP a INNER JOIN Vendor b and it should work.