Runtime error 3075

[Homegrownandy]

I have previously had some very similar code working. I can't figure out what the problem is with this one (see below):

syntax error (missing operator) in query expression.

Private Sub Command37_Click()
Dim Permits As String
Dim db As Database
Set db = CurrentDb
Dim rst As DAO.Recordset
Permits = "SELECT DISTINCT Site_Risks.selected, PERMIT_LIST.PERMIT_NAME, PPE_LIST.PPE_LIST"
Permits = Permits & "FROM (PPE_LIST INNER JOIN (PERMIT_LIST INNER JOIN Site_Risk_Categories ON PERMIT_LIST.PERMIT_ID = Site_Risk_Categories.Permit_ID) ON PPE_LIST.PPE_ID = Site_Risk_Categories.PPE_ID) INNER JOIN Site_Risks ON Site_Risk_Categories.Risk_category_ID = Site_Risks.Category_ID"
Permits = Permits & "WHERE (((Site_Risks.selected)=True));"
Set rst = db.OpenRecordset(Permits)


End Sub

A difference between this and the other working code is this code can return multiple values. The other time I used it, it was for one record only. I'm not sure that is the issue but its worth mentioning. More likely its some formatting issue with the code.
the error highlights this line:


Set rst = db.OpenRecordset(sSql)

any thoughts appreciated as I'm stumped.

[Micron]

You aren't using spaces between key words so one of your results will be PPE_LIST.PPE_LISTPermits = PermitsFROM
Get in the habit of having the space at the end or beginning of a line and be consistent with the choice. That should help.
This would likely be obvious if you also adopted the habit of printing the sql variable to the immediate window for review during testing or when it fails.

[Homegrownandy]

I knew it would be something simple. Also thanks for the advice. Thanks a lot!

[Micron]

You're welcome. For the benefit of others who might not be familiar with that style of concatenation (which I prefer over line continuation) or printing to the immediate window here's your adapted code as an example:

Permits = "SELECT DISTINCT Site_Risks.selected, PERMIT_LIST.PERMIT_NAME, PPE_LIST.PPE_LIST FROM "
Permits = Permits & "(PPE_LIST INNER JOIN (PERMIT_LIST INNER JOIN Site_Risk_Categories ON PERMIT_LIST.PERMIT_ID = "
Permits = Permits & "Site_Risk_Categories.Permit_ID) ON PPE_LIST.PPE_ID = Site_Risk_Categories.PPE_ID) INNER JOIN "
Permits = Permits & "Site_Risks ON Site_Risk_Categories.Risk_category_ID = Site_Risks.Category_ID WHERE " 
Permits = Permits & "(((Site_Risks.selected)=True));"
Debug.Print Permits

When no longer needed, comment out the Debug.Print line

[ssanfu]

I, too, prefer concatenation over line continuation.
But I have the spaces at the beginning of the line instead of the end.
I also have the SQL keywords at the beginning of the line and, if a clause is really long, I sometimes add blank lines for readability.

Permits = "SELECT DISTINCT Site_Risks.selected, PERMIT_LIST.PERMIT_NAME, PPE_LIST.PPE_LIST"

Permits = Permits & " FROM (PPE_LIST INNER JOIN (PERMIT_LIST INNER JOIN Site_Risk_Categories ON PERMIT_LIST.PERMIT_ID ="
Permits = Permits & " Site_Risk_Categories.Permit_ID) ON PPE_LIST.PPE_ID = Site_Risk_Categories.PPE_ID) INNER JOIN"
Permits = Permits & " Site_Risks ON Site_Risk_Categories.Risk_category_ID = Site_Risks.Category_ID" 

Permits = Permits & " WHERE (((Site_Risks.selected)=True));"
'  Permits = Permits & " WHERE (((Site_Risks.selected)=False));"
Debug.Print Permits

It is also easier to comment out a line to try a different line. (see the WHERE lines)



Note: the Blue highlighting isn't available in the IDE. It is here just for emphasis.