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

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

zimmer9Asked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
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
 
tigin44Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.