JOIN in Access vs SQL Server

[Muddmuse]

I have an .asp page with embedded sql. The following statement works fine going against SQL Server but I get "Syntax error in FROM clause" when run against Access. I've gone into the relationship properties dialogs to set up joins but still get the same error. Any ideas on what the cause might be?

SELECT r.record_name, r.date, r.comments, ISNULL(t.tune_name,'N/A'), a.artist_name, a.artist_id
FROM record r
JOIN record_x_artist ra on r.record_id = ra.record_id
JOIN artists a on ra.artist_id = a.artist_id
LEFT JOIN record_x_tune rt on r.record_id = rt.record_id
LEFT JOIN tunes t on rt.tune_id = t.tune_id
WHERE r.record_id = 3

Thanks in advance.

 

[cmmrfrds]

Try changing the isnull to the nz function which provides the same functionality. double quotes in access. You might need the as keyword for aliases

SELECT r.record_name, r.date, r.comments, nz(t.tune_name,"N/A&quot, a.artist_name, a.artist_id
FROM record as r
JOIN record_x_artist ra on r.record_id = ra.record_id
JOIN artists as a on ra.artist_id = a.artist_id
LEFT JOIN record_x_tune as rt on r.record_id = rt.record_id
LEFT JOIN tunes as t on rt.tune_id = t.tune_id
WHERE r.record_id = 3

 

[Muddmuse]

I keep getting this error:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.

Do I need to do anything with the Edit Relationships dialogs?

Any other ideas?

 

[cmmrfrds]

Add inner to the inner joins

FROM record as r
INNER JOIN record_x_artist ra on r.record_id = ra.record_id
INNER JOIN artists as a on ra.artist_id = a.artist_id

 

[Muddmuse]

Now I'm getting the following error:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'r.record_id = ra.record_id INNER JOIN artists AS a ON ra.artist_id = a.artist_id LEFT JOIN record_x_tune AS rt ON r.record_id = rt.record_id LEFT JOIN tunes AS t ON rt.tune_id = t.tune_id'.

More thoughts?