Missing operator - SQL query in VBA

M

Maver1ck666

Hi everyone,

I have put together an sql statement to be run in a module but I am getting
a run time 3075 (missing operator) message when I try to run it.

The code is:

DoCmd.RunSQL "INSERT INTO [Tbl 1 All Data Merged And Populated] ( [Fund
Name], [Account Type], [Account Number], [Account Name], MannerOfAddress, " _
& "Salutation, ClAddLine1, ClAddLine2, ClAddLine3, ClAddLine4,
ClAddLine5, ClAddLine6, ClPostCode ) " _
& "SELECT TblInportSIS.[Fund Name], TblInportSIS.[Legal Wrapper],
TblInportSIS.[Policy ID], " _
& "IIf(IsNull([Investor Title]),[Investor Name],[Investor Title] + SPACE
[Investor Name] + SPACE [Investor Surname]) AS [Account Name], " _
& "IIf(IsNull([Investor Title]),[Investor Name],[Investor Title] + SPACE
(Left$([Investor Name],1)) + SPACE [Investor Surname]) AS MannerOfAddress, " _
& "IIf(IsNull([Investor Title]), '" & Sirs & "',[Investor Title] + SPACE
[Investor Surname]) AS Salutation, " _
& "TblInportSIS.Address1, TblInportSIS.Address2, TblInportSIS.Address3,
TblInportSIS.City, TblInportSIS.County, TblInportSIS.Country,
TblInportSIS.[Post Code] " _
& "FROM TblInportSIS;"

Any ideas what I have missed please as I have been staring at it for a while
and cant figure it out for the life of me.

Cheers all!
 
R

Roger Carlson

The best thing to do is assign your SQL statement to a string and follow
that with a Debug statement:

strSQL = "INSERT INTO [Tbl 1 All Data Merged And Populated] ( [Fund
Name], [Account Type], [Account Number], [Account Name], MannerOfAddress, "
_
& "Salutation, ClAddLine1, ClAddLine2, ClAddLine3, ClAddLine4,
ClAddLine5, ClAddLine6, ClPostCode ) " _
& "SELECT TblInportSIS.[Fund Name], TblInportSIS.[Legal Wrapper],
TblInportSIS.[Policy ID], " _
& "IIf(IsNull([Investor Title]),[Investor Name],[Investor Title] + SPACE
[Investor Name] + SPACE [Investor Surname]) AS [Account Name], " _
& "IIf(IsNull([Investor Title]),[Investor Name],[Investor Title] + SPACE
(Left$([Investor Name],1)) + SPACE [Investor Surname]) AS MannerOfAddress, "
_
& "IIf(IsNull([Investor Title]), '" & Sirs & "',[Investor Title] + SPACE
[Investor Surname]) AS Salutation, " _
& "TblInportSIS.Address1, TblInportSIS.Address2, TblInportSIS.Address3,
TblInportSIS.City, TblInportSIS.County, TblInportSIS.Country,
TblInportSIS.[Post Code] " _
& "FROM TblInportSIS;"

Debug.Print strSQL

DoCmd.RunSQL strSQL

Put a breakpoint on the DoCmd line and execute the procedure. The code will
stop before the docmd is executed, but your SQL *as the query engine will
see it* will display in the Immediate Window. Copy and paste that into a
Query and see what sort of error message you get.

Once you figure out the error, comment out the Debug line. (Or delete it,
but you might need it again sometime.)

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Maver1ck666 said:
Hi everyone,

I have put together an sql statement to be run in a module but I am
getting
a run time 3075 (missing operator) message when I try to run it.

The code is:

DoCmd.RunSQL "INSERT INTO [Tbl 1 All Data Merged And Populated] ( [Fund
Name], [Account Type], [Account Number], [Account Name], MannerOfAddress,
" _
& "Salutation, ClAddLine1, ClAddLine2, ClAddLine3, ClAddLine4,
ClAddLine5, ClAddLine6, ClPostCode ) " _
& "SELECT TblInportSIS.[Fund Name], TblInportSIS.[Legal Wrapper],
TblInportSIS.[Policy ID], " _
& "IIf(IsNull([Investor Title]),[Investor Name],[Investor Title] +
SPACE
[Investor Name] + SPACE [Investor Surname]) AS [Account Name], " _
& "IIf(IsNull([Investor Title]),[Investor Name],[Investor Title] +
SPACE
(Left$([Investor Name],1)) + SPACE [Investor Surname]) AS MannerOfAddress,
" _
& "IIf(IsNull([Investor Title]), '" & Sirs & "',[Investor Title] +
SPACE
[Investor Surname]) AS Salutation, " _
& "TblInportSIS.Address1, TblInportSIS.Address2, TblInportSIS.Address3,
TblInportSIS.City, TblInportSIS.County, TblInportSIS.Country,
TblInportSIS.[Post Code] " _
& "FROM TblInportSIS;"

Any ideas what I have missed please as I have been staring at it for a
while
and cant figure it out for the life of me.

Cheers all!
Click to expand...
 
D

Douglas J. Steele

Roger's advise is sound.

One thing I noticed in your code, though, is that

[Investor Title] + SPACE (Left$([Investor Name],1)) + SPACE [Investor
Surname]

looks suspect. What is SPACE? I'm assuming it's a constant containing a
space, in which case you need concatenation characters after it as well

[Investor Title] + SPACE + (Left$([Investor Name],1)) + SPACE + [Investor
Surname]
 
Top