Could someone help me on this one, please?
I am trying to add a field from a third table to a SQL that displays data in a ListBox. It populated the listbox fine until I tried adding the third table (tblCHDPStatus).

Can one use the "AND" operator in the FROM clause? If so, did I do that wrong? This statement throws a "error in FROM" clause error.

SELECT [tblPractice].[LegalName], [tblCHDPStatus].[CHDP Status], [tblCity].[City], [tblPractice].[PracticeNum] FROM (tblCHDPStatus INNER JOIN tblPractice ON [tblCHDPStatus].[CHDPStatusID]=[tblPractice].[CHDPStatusID]) AND tblCity INNER JOIN tblPractice ON [tblCity].[CityID]=[tblPractice].[CityPM177ID] WHERE ((Not ([tblPractice].[LegalName]) Is Null)) ORDER BY [tblPractice].[LegalName], [tblPractice].[CityPM177ID], [tblCity].[City];

Thanks,
Loralee

Hi Loralee,

I have no idea who invented INNER JOIN syntax, but I really wish they hadn't.

This is SO much clearer:

SELECT p.LegalName, ch.[CHDP Status], c.City, p.PracticeNum
FROM tblCHDPStatus ch, tblPractice p, tblCity c
WHERE ch.CHDPStatusID=p.CHDPStatusID
AND c.CityID=p.CityPM177ID
AND p.LegalName IS NOT NULL
ORDER BY p.LegalName, p.CityPM177ID, c.City

I havn't tested it, so I'm not sure it gives you what you want, but ALL "inner join" syntax SQL statements can be written so much more easily by using WHERE clauses. Also, table aliases really simplify your script, and avoid field names with spaces at any cost.

If the above doesn't work, let me know an I'll tweak it.

HTH,

Bob

Thanks, Bob. That runs perfectly.

Using the "WHERE" and aliasing does make it much easier to read. (Was a little surprized to see the space.....it wasn't supposed to be there). I've got several more I can practice on, and some code to clean up, too!:D

Thanks again,
Loralee