By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,066 Members | 1,170 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,066 IT Pros & Developers. It's quick & easy.

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

P: 39
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnStep15_Click()
  2.     ' make table query for Zones missing Fire ATI's
  3.     Dim SQL15 As String
  4.         SQL15 = "SELECT [SYSTEM WORKING].[ati hours], ZONE_WORKING.NOTES, ZONE_WORKING.NOTES_answers, ZONE_WORKING.DO_NOT_LOAD, " & _
  5.                     "ZONE_WORKING.cs_no, ZONE_WORKING.zone_id, ZONE_WORKING.event_id, ZONE_WORKING.descr, ZONE_WORKING.comment " & _
  6.                     "INTO [ZONES  WITH FIRE MISSING ATI] FROM [SITE WORKING] INNER JOIN (ZONE_WORKING INNER JOIN [SYSTEM WORKING] ON " & _
  7.                     "ZONE_WORKING.system_no = [SYSTEM WORKING].[system no]) ON [SITE WORKING].site_no = [SYSTEM WORKING].[site no] " & _
  8.                 "WHERE ((([SYSTEM WORKING].[ati hours]) Is Null) ((ZONE_WORKING.event_id) Not Like 'E*' AND (ZONE_WORKING.event_id) Not Like 'FA' " & _
  9.                     "AND (ZONE_WORKING.event_id) Not Like 'FB' AND (ZONE_WORKING.event_id) Not Like 'FH' AND (ZONE_WORKING.event_id) Not Like 'FI' " & _
  10.                     "AND (ZONE_WORKING.event_id) Not Like 'FJ' AND (ZONE_WORKING.event_id) Not Like 'FK' AND (ZONE_WORKING.event_id) Not Like 'FR' " & _
  11.                     "AND (ZONE_WORKING.event_id) Not Like 'FS' AND (ZONE_WORKING.event_id) Not Like 'FT' AND (ZONE_WORKING.event_id) Not Like 'FU'" & _
  12.                     "AND (ZONE_WORKING.event_id) Not Like 'FX' AND (ZONE_WORKING.event_id) Not Like 'FY') AND ((ZONE_WORKING.NOTES) Is Null) " & _
  13.                     "AND ((ZONE_WORKING.DO_NOT_LOAD) Is Null) AND (((ZONE_WORKING.descr) Like '*fire*' Or (ZONE_WORKING.descr) Like '*water*' " & _
  14.                     "Or (ZONE_WORKING.descr) Like '*smoke*' Or (ZONE_WORKING.descr) Like '*heat*') AND (ZONE_WORKING.descr) Not Like 'fire su*' " & _
  15.                     "AND (ZONE_WORKING.descr) Not Like 'fire tr*') AND ((ZONE_WORKING.comment) Is Null Or (ZONE_WORKING.comment) Not Like '*co*' " & _
  16.                     "AND (ZONE_WORKING.comment) Not Like '*tr*' AND (ZONE_WORKING.comment) Not Like '*su*')); "
  17.  
  18. Me.Message = "Copying..."
  19. Me.Repaint
  20.     DoCmd.RunSQL SQL15
  21. Me.Message = "Completed Step 15"
  22. Me.Repaint
  23.  
  24. End Sub
  25.  
Thanks in advance!
Apr 29 '09 #1
Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,679
@timber910
Looks like you forgot an AND Clause in Line #8:
Expand|Select|Wrap|Line Numbers
  1.  "WHERE ((([SYSTEM WORKING].[ati hours]) Is Null) ((ZONE_WORKING.event_id) Not Like 'E*' AND (ZONE_WORKING.event_id) Not Like 'FA' " & _
Should Be:
Expand|Select|Wrap|Line Numbers
  1.  "WHERE ((([SYSTEM WORKING].[ati hours]) Is Null) And((ZONE_WORKING.event_id) Not Like 'E*' AND (ZONE_WORKING.event_id) Not Like 'FA' " & _
Apr 29 '09 #2

P: 39
DAMN!! figures i just needed another set of eyes! Thanks for the help ADezii!!
Apr 29 '09 #3

ADezii
Expert 5K+
P: 8,679
@timber910
Not a problem - some times another set of eyes is all that it takes (LOL)!
Apr 29 '09 #4

Post your reply

Sign in to post your reply or Sign up for a free account.