zimmer9
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'.
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 "
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
CurrentProject.Connection.
" 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 "