[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to avoid a syntax error, Missing Operator on a UNION ALL statement ?

Posted on 2008-11-07
2
Medium Priority
?
574 Views
Last Modified: 2013-11-27
I am creating an MDB type Access application.
I used the following statement in the Attach Code Snippet in my VBA code:

Do you know why it caused the following statement ?

Run-time error '-2147217900(80040E14')

Syntax error (missing operator) in query expression 'DE05 Is Not Null Union ALL SELECT Account AS Account, Client AS Client, Address AS Address, JA06 AS [STATEMENT PERIOD] FROM tblPWM WHERE JA06 is Not Null'.  




CurrentProject.Connection.Execute "INSERT INTO tblPWMMod " & _
"  SELECT Account AS Account, Client AS Client, Address AS Address, DE05 AS [STATEMENT PERIOD] FROM tblPWM WHERE DE05 Is Not Null " & _
"  Union ALL SELECT Account AS Account, Client AS Client, Address AS Address, JA06 AS [STATEMENT PERIOD] FROM tblPWM WHERE JA06 is Not Null "

Open in new window

0
Comment
Question by:zimmer9
2 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 total points
ID: 22907652
CurrentProject.Connection.Execute "INSERT INTO tblPWMMod " & _
    "SELECT Account, Client, Address, [Statement Period] FROM (" & _
    "SELECT Account AS Account, Client AS Client, Address AS Address, DE05 AS [STATEMENT PERIOD] FROM tblPWM WHERE DE05 Is Not Null " & _
    "Union ALL SELECT Account AS Account, Client AS Client, Address AS Address, JA06 AS [STATEMENT PERIOD] FROM tblPWM WHERE JA06 is Not Null)"
0
 
LVL 26

Expert Comment

by:tigin44
ID: 22907660
Try this

CurrentProject.Connection.Execute "INSERT INTO tblPWMMod " & _
"  SELECT Account AS Account, Client AS Client, Address AS Address, DE05 AS "STATEMENT PERIOD" FROM tblPWM WHERE DE05 Is Not Null " & _
"  Union ALL SELECT Account AS Account, Client AS Client, Address AS Address, JA06 AS "STATEMENT PERIOD" FROM tblPWM WHERE JA06 is Not Null "
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question