query copied from SQL View does not work in VBA

[DMUM via AccessMonster.com]

Hello, I created a query in the MS Access query window and then copied the
SQL view into my VBA code. Although I've added the quotes and the sql string
designation, I can get the code to be accetable to my VBA code (I have the
code colors on). Here is my query:

strSQL = "INSERT INTO tblTemp_PRSADefLoad (intPRSA, PRSA_Definition,
PRSA_Type) " & _
"SELECT Left([I_PRSA_Reference],InStrRev([I_PRSA_Reference]," ")+2)
AS PRSA, " & _
"tblImportExcel.PRSA_Business_Definition, IIf([PRSA]<>"","Business",
"") AS prsaType " & _
"FROM tblImportExcel;"

This works fine using the MS Access Query window.

Since I don't have to have the portion: SELECT Left([I_PRSA_Reference],
InStrRev([I_PRSA_Reference]," ")+2) AS PRSA, tblImportExcel.
PRSA_Business_Definition,

I re-wrote the query - again in the MS Access query window to look like this:

strSql = "INSERT INTO tblTemp_PRSADefLoad ( intPRSA, PRSA_Definition,
PRSA_Type ) " & _
"SELECT tblImportExcel.PRSA_Business, tblImportExcel.PRSA_Business_Definition,
" & _
"IIf([PRSA_Business]<>"","Business","") AS prsaType " & _
"FROM tblImportExcel;"

This still will not work in my VBA code. I removed the IIF portion of the
code and it finally was accepted by the VBA code window (changed the right
color), so I'm guessing in this case it has to do with the IIF statement, but
I can't for the life of me figure out what is wrong with it.

 

[Pendragon]

When you use quotes in SQL and try to use them in VBA, you have to double
double-quote your text, so your IIF statement should look like this:

IIf([PRSA_Business]<>"""",""Business"","""") AS prsaType

 

[Douglas J Steele]

It's because of the quotes: when you want to include quotes inside a quote,
you have to double them up. Try changing

"IIf([PRSA_Business]<>"","Business","") AS prsaType " & _

to

"IIf([PRSA_Business]<>"""",""Business"","""") AS prsaType " & _

or else replace the inner double quotes with single quotes:

"IIf([PRSA_Business]<>'','Business','') AS prsaType " & _

Exagerated for clarity, that second one is

"IIf([PRSA_Business]<> ' ' ,'Business', ' ' ) AS prsaType " & _


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello, I created a query in the MS Access query window and then copied the
SQL view into my VBA code. Although I've added the quotes and the sql string
designation, I can get the code to be accetable to my VBA code (I have the
code colors on). Here is my query:

strSQL = "INSERT INTO tblTemp_PRSADefLoad (intPRSA, PRSA_Definition,
PRSA_Type) " & _
"SELECT Left([I_PRSA_Reference],InStrRev([I_PRSA_Reference]," ")+2)
AS PRSA, " & _
"tblImportExcel.PRSA_Business_Definition,

"") AS prsaType " & _
"FROM tblImportExcel;"

This works fine using the MS Access Query window.

Since I don't have to have the portion: SELECT Left([I_PRSA_Reference],
InStrRev([I_PRSA_Reference]," ")+2) AS PRSA, tblImportExcel.
PRSA_Business_Definition,

I re-wrote the query - again in the MS Access query window to look like this:

strSql = "INSERT INTO tblTemp_PRSADefLoad ( intPRSA, PRSA_Definition,
PRSA_Type ) " & _
"SELECT tblImportExcel.PRSA_Business, tblImportExcel.PRSA_Business_Definition,
" & _
"IIf([PRSA_Business]<>"","Business","") AS prsaType " & _
"FROM tblImportExcel;"

This still will not work in my VBA code. I removed the IIF portion of the
code and it finally was accepted by the VBA code window (changed the right
color), so I'm guessing in this case it has to do with the IIF statement, but
I can't for the life of me figure out what is wrong with it.

 

[Marshall Barton]

Hello, I created a query in the MS Access query window and then copied the
SQL view into my VBA code. Although I've added the quotes and the sql string
designation, I can get the code to be accetable to my VBA code (I have the
code colors on). Here is my query:

strSQL = "INSERT INTO tblTemp_PRSADefLoad (intPRSA, PRSA_Definition,
PRSA_Type) " & _
"SELECT Left([I_PRSA_Reference],InStrRev([I_PRSA_Reference]," ")+2)
AS PRSA, " & _
"tblImportExcel.PRSA_Business_Definition, IIf([PRSA]<>"","Business",
"") AS prsaType " & _
"FROM tblImportExcel;"

This works fine using the MS Access Query window.

Since I don't have to have the portion: SELECT Left([I_PRSA_Reference],
InStrRev([I_PRSA_Reference]," ")+2) AS PRSA, tblImportExcel.
PRSA_Business_Definition,

I re-wrote the query - again in the MS Access query window to look like this:

strSql = "INSERT INTO tblTemp_PRSADefLoad ( intPRSA, PRSA_Definition,
PRSA_Type ) " & _
"SELECT tblImportExcel.PRSA_Business, tblImportExcel.PRSA_Business_Definition,
" & _
"IIf([PRSA_Business]<>"","Business","") AS prsaType " & _
"FROM tblImportExcel;"

This still will not work in my VBA code. I removed the IIF portion of the
code and it finally was accepted by the VBA code window (changed the right
color), so I'm guessing in this case it has to do with the IIF statement, but
I can't for the life of me figure out what is wrong with it.

Anytime you have quotes inside of other quotes, you need to
double up the interior quotes:

"SELECT Left([I_PRSA_Reference],
InStrRev([I_PRSA_Reference], "" "")+2) AS PRSA, " & _

 

[DMUM via AccessMonster.com]

Thank you, I knew it was something very small I was missing. I didn't think
of "Business" being used as a variable since it isn't something I name in my
VBA code.

Thank you to pendragon

Thank you both for such a quick response.

It's because of the quotes: when you want to include quotes inside a quote,
you have to double them up. Try changing

"IIf([PRSA_Business]<>"","Business","") AS prsaType " & _

to

"IIf([PRSA_Business]<>"""",""Business"","""") AS prsaType " & _

or else replace the inner double quotes with single quotes:

"IIf([PRSA_Business]<>'','Business','') AS prsaType " & _

Exagerated for clarity, that second one is

Hello, I created a query in the MS Access query window and then copied the
SQL view into my VBA code. Although I've added the quotes and the sql string

[quoted text clipped - 28 lines]

color), so I'm guessing in this case it has to do with the IIF statement, but
I can't for the life of me figure out what is wrong with it.