Syntax error in FROM clause

Question

The following SQL works just fine as the rowsource for a list box in an adp. It also works in SQL Server (with the "dbo_" part removed from the table names). However, it doesn't work in the accdb I'm converting to and gives me a "Syntax error in FROM clause" error.

select EID as [ID], s.Definition as [Site], fname + ' ' + lname as [Name]

from dbo_lk_ID as m join dbo_lk_site as s on m.site = s.site join dbo_RAND as r on m.EID = r.ID

where len(ID) >= 8

order by Len(EID) desc, EID desc

I would appreciate your help.

Answer 1

Try this with INNER and parenthesis --

select EID as [ID], s.Definition as [Site], fname + ' ' + lname as [Name]

from (dbo_lk_ID as m INNER join dbo_lk_site as s on m.site = s.site) INNER join dbo_RAND as r on m.EID = r.ID

where len(ID) >= 8

order by Len(EID) desc, EID desc;

Answer 2

With an ACCDB you have to use JET syntax (unless you check the option to use ANSI SQL)

Bill, that sounds interesting: How exactly do you check the option to use ANSI SQL? I looked and couldn't find it anywhere.

Thanks.

Answer 3

Thanks everyone! I first tried changing the join to an inner join but I got a "syntax error (missing operator) in query expression" error. I then added the parentheses where Karl indicated and it worked beautifully.

Answer 4

With an ACCDB you have to use JET syntax (unless you check the option to use ANSI SQL)

SELECT   EID AS [ID]

, s.Definition AS [Site]

, fname & ' ' & lname AS [Name]

FROM     dbo_lk_ID AS m

INNER JOIN dbo_lk_site AS s

ON       m.site = s.site

INNER JOIN dbo_RAND AS r

ON       m.EID = r.ID

WHERE    LEN(ID) >= 8

ORDER BY LEN(EID) DESC

, EID DESC

You don't have to format it the way I did, but the syntax should work.

Answer 5

Access uses INNER JOIN instead of just JOIN