Invalid use of ., ! or () - runtime error 3075

[timber910]

Hello All,

I'm in need of help here. I have build my query in a query builder in access as a select query. Query runs fine. Changed it to a make table query. Query makes table ok. Copy and pasted the SQL into my VBA on a form and bam! Run Time error 3075. I've checked and re-checked but I'm not any closer to finguring out where i'm off. If someone could help I would be so grateful! My full code is below. My error message states "Invalid use of '.', '!', or '()'. in query expression '((([SYSTEM WORKING].[ati hours]) ".... after the WHERE clause.

Private Sub btnStep15_Click()
    ' make table query for Zones missing Fire ATI's
    Dim SQL15 As String
        SQL15 = "SELECT [SYSTEM WORKING].[ati hours], ZONE_WORKING.NOTES, ZONE_WORKING.NOTES_answers, ZONE_WORKING.DO_NOT_LOAD, " & _
                    "ZONE_WORKING.cs_no, ZONE_WORKING.zone_id, ZONE_WORKING.event_id, ZONE_WORKING.descr, ZONE_WORKING.comment " & _
                    "INTO [ZONES – WITH FIRE MISSING ATI] FROM [SITE WORKING] INNER JOIN (ZONE_WORKING INNER JOIN [SYSTEM WORKING] ON " & _
                    "ZONE_WORKING.system_no = [SYSTEM WORKING].[system no]) ON [SITE WORKING].site_no = [SYSTEM WORKING].[site no] " & _
                "WHERE ((([SYSTEM WORKING].[ati hours]) Is Null) ((ZONE_WORKING.event_id) Not Like 'E*' AND (ZONE_WORKING.event_id) Not Like 'FA' " & _
                    "AND (ZONE_WORKING.event_id) Not Like 'FB' AND (ZONE_WORKING.event_id) Not Like 'FH' AND (ZONE_WORKING.event_id) Not Like 'FI' " & _
                    "AND (ZONE_WORKING.event_id) Not Like 'FJ' AND (ZONE_WORKING.event_id) Not Like 'FK' AND (ZONE_WORKING.event_id) Not Like 'FR' " & _
                    "AND (ZONE_WORKING.event_id) Not Like 'FS' AND (ZONE_WORKING.event_id) Not Like 'FT' AND (ZONE_WORKING.event_id) Not Like 'FU'" & _
                    "AND (ZONE_WORKING.event_id) Not Like 'FX' AND (ZONE_WORKING.event_id) Not Like 'FY') AND ((ZONE_WORKING.NOTES) Is Null) " & _
                    "AND ((ZONE_WORKING.DO_NOT_LOAD) Is Null) AND (((ZONE_WORKING.descr) Like '*fire*' Or (ZONE_WORKING.descr) Like '*water*' " & _
                    "Or (ZONE_WORKING.descr) Like '*smoke*' Or (ZONE_WORKING.descr) Like '*heat*') AND (ZONE_WORKING.descr) Not Like 'fire su*' " & _
                    "AND (ZONE_WORKING.descr) Not Like 'fire tr*') AND ((ZONE_WORKING.comment) Is Null Or (ZONE_WORKING.comment) Not Like '*co*' " & _
                    "AND (ZONE_WORKING.comment) Not Like '*tr*' AND (ZONE_WORKING.comment) Not Like '*su*')); "
 
Me.Message = "Copying..."
Me.Repaint
    DoCmd.RunSQL SQL15
Me.Message = "Completed Step 15"
Me.Repaint
 
End Sub
 

Thanks in advance!

[ADezii]

@timber910
Looks like you forgot an AND Clause in Line #8:

 "WHERE ((([SYSTEM WORKING].[ati hours]) Is Null) ((ZONE_WORKING.event_id) Not Like 'E*' AND (ZONE_WORKING.event_id) Not Like 'FA' " & _

Should Be:

 "WHERE ((([SYSTEM WORKING].[ati hours]) Is Null) And((ZONE_WORKING.event_id) Not Like 'E*' AND (ZONE_WORKING.event_id) Not Like 'FA' " & _

[timber910]

DAMN!! figures i just needed another set of eyes! Thanks for the help ADezii!!

[ADezii]

@timber910
Not a problem - some times another set of eyes is all that it takes (LOL)!