Using 6.23.03.24, can't consistently get line breaks before INNER JOIN or ON with the following SQL. I am guessing this has to do with how MS Access likes to put parentheses around things:
SELECT vBDSTEPS_SubjectID_CenterID.SubjectID,
C.Code,
Nz([IsEligible], 0) AS Eligible,
tblPatientDX.Probable,
False AS Excluded,
tblPatientDX.DxVerbatim,
ISNULL([Codes].[Code]) AS [Unknown],
[IDSITE]=70
AND NOT ISNULL([Codes].[Code]) AS Selected
FROM (vBDSTEPS_SubjectID_CenterID INNER JOIN ((tblPatientDX INNER JOIN
(SELECT DXCodeID,
cstr(DxCode) AS Code
FROM tlkpDXCodes) AS C ON tblPatientDX.DXCodeID = C.DXCodeID) INNER JOIN tblPatientAbstract ON tblPatientDX.AbstractID = tblPatientAbstract.AbstractID) ON vBDSTEPS_SubjectID_CenterID.CenterID = tblPatientAbstract.CenterID)
LEFT JOIN Codes
ON C.Code = Codes.Code;
Here is what my linebreak options for SELECT look like and the SQL above has been formatted with these settings:
-Bruce
Hi @bbhulsey, please have a look to this new formatting for JOIN in MsAccess
If you like it, you can download the new Desktop release 6.23.04.06 from here https://www.sqlinform.com/download-sql-formatter-windows-app/
and give it a try. Please do not forget to choose "MsAccess" as DB
Regards
Guido
@admin Hello Guido, this appears to work very well. Thanks again for your help and hard work!