Access doesn't like this query

[greg_quinn]

I have this query, but MS Access 2000 doesn't seem to like the second INNER JOIN, it tells me 'Synax, missing operator in query expression'

Is my query incorrect, or does Access not handle multiple joins? If so, how could I rewrite my query?

SELECT sms_contacts.ContactName + ',' + sms_outbox.Message
FROM sms_contacts
INNER JOIN sms_recipients ON sms_contacts.ContactID = sms_recipients.ContactID
INNER JOIN sms_outbox ON sms_recipients.smsID = sms_outbox.smsoutboxID

[Lil Ms Squirrel]

I recreated your table structure and ran it through the Access query designer which gave me the same error.

However, when I switched to design view and created the joins visually, Access provided me with this SQL string:

SELECT sms_contacts.ContactName+','+sms_outbox.Message AS Expr1
FROM (sms_contacts INNER JOIN sms_recipients ON sms_contacts.ContactID = sms_recipients.ContactID) INNER JOIN sms_outbox ON sms_recipients.SMSID = sms_outbox.smsoutboxid

Access has put your joins in in a different order. I don't have time to check this out in SQL Query Analyser, but it should help you out with Access in the meantime.

If I can suss it out elsewere then I'll give you another shout.

[BruceG]

Access requires the parentheses around the join expressions as in Lil Ms' code. In SQL Server, you can get away without the parens, but Access is pickier.

[greg_quinn]

Thanks people. One has to wonder why can't MS just make its access interpreter in the same style as SQL Servers? I mean for sure, take out some functionality, but why change the syntax requirements?

[greg_quinn]

Bah, now when I finally try this query (on a Sunday night) in Access, it asks me for the ContactID???

In my actual program, it gives me an error, saying no value given for one or more required parameters..

I don't see anything wrong with the query...

Thanks

[RobDog888]

sms_contacts.ContactID and sms_recipients.ContactID exist in both tables
and are spelled like in your query?

[greg_quinn]

sms_contacts.ContactID and sms_recipients.ContactID exist in both tables and are spelled like in your query?

I'm not sure what you are asking, but yes, contactID field exists in both tables, my query is...

SELECT sms_contacts.ContactName+','+sms_outbox.Message AS Expr1
FROM (sms_contacts INNER JOIN sms_recipients ON sms_contacts.ContactID = sms_recipients.ContactID) INNER JOIN sms_outbox ON sms_recipients.SMSID = sms_outbox.smsoutboxid

[szlamany]

Originally posted by greg_quinn
Thanks people. One has to wonder why can't MS just make its access interpreter in the same style as SQL Servers? I mean for sure, take out some functionality, but why change the syntax requirements?

ACCESS is MS home-grown attempt at SQL. MS SQL SERVER 2000 is a product developed by SYBASE - purchased by MS - and now taking over the SQL enterprise market (just don't ask Larry Elison what he thinks about that)