Link to home
Start Free TrialLog in
Avatar of zimmer9
zimmer9Flag for United States of America

asked on

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

ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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 "