INSERT INTO SELECT statement

[MarvinM]

[SOLVED] INSERT INTO SELECT statement

Hello Experts,

Here's my challenge. I have an append query that contains an IIF statement. I want to code that into a VBA function. The SQL view of the query looks like this:

INSERT INTO tmpAvailInv ( NUID, Inv_Name, F_Name, M_Name, L_Name, Role )
SELECT tblPeople.NUID, tblPeople.[F_name] & IIf(IsNull([M_Name])," "," " & [M_Name] & " ") & [L_Name] AS Inv_Name, tblPeople.F_Name, tblPeople.M_Name, tblPeople.L_Name, tblPeople.Role
FROM tblPeople
WHERE (((tblPeople.Role)="Investigator") AND ((tblPeople.Archive)=False));

What I wrote for the VBA code is this:

    Dim strSQL As String
    Dim db As Database
 
    Set db = CurrentDb
 
    strSQL = "INSERT INTO tmpAvailInv ( NUID, Inv_Name, F_Name, M_Name, L_Name, Role ) " & _
        SELECT tblPeople.NUID, " & _
        tblPeople.[F_name] & IIf(IsNull([M_Name])," "," " & [M_Name] & " ") & [L_Name] AS Inv_Name, " & _
        tblPeople.F_Name, tblPeople.M_Name, tblPeople.L_Name, tblPeople.Role " & _
        FROM tblPeople " & _
        WHERE (((tblPeople.Role)="Investigator") AND ((tblPeople.Archive)=False));"
 
    db.Execute (strSQL)
    db.Close

Where it chokes is on the IIF statement with the double-quotes in it. I've tried several combinations with single quotes and double double-quotes. I'm just not getting it. Can any of you please tell me what I'm doing wrong? I know I'm doing something wrong. I just can't figure out what. :banghead:

Thank you,
Marvin M

 

[jdraw]

Your sql is a string in vba delimited by quotes("). When you have quotes within quotes, either double the quotes("") or change the internal quotes to single quote (')

Try (untested)

strSQL = "INSERT INTO tmpAvailInv ( NUID, Inv_Name, F_Name, M_Name, L_Name, Role ) " & _
        " SELECT tblPeople.NUID, " & _
        [B]"[/B] tblPeople.[F_name] & IIf(IsNull([M_Name]),"" "","" "" & [M_Name] & "" "") & [L_Name] AS Inv_Name, " & _
        [B]"[/B] tblPeople.F_Name, tblPeople.M_Name, tblPeople.L_Name, tblPeople.Role " & _
        [B]"[/B] FROM tblPeople " & _
        [B]"[/B] WHERE (((tblPeople.Role)="Investigator") AND ((tblPeople.Archive)=False));"

You missed some required quotes at beginning of stings.

 

[MarvinM]

JD,

Thanks for the advice. I had the quotes at the beginning of every line in my string. I don't know how it got dropped when I pasted it into a CODE box. I know the whole string is good except the IIF part. If I remove this "IIf(IsNull([M_Name]),"" "","" "" & [M_Name] & "" "") &" then the SQL query runs fine. I could replace that whole mess with " " and just have FullName = FirstName LastName, but I really want to get the middle name in there if one exists.
FYI - I also tried going the Nz() route, but ended up with a double space between FirstName and LastName if there was no MiddleName. So that's why I went with the IIF.

 

[MarvinM]

[SOLVED] INSERT INTO SELECT statement

...the SQL query runs fine.

Aaagh!


JD,

You are right. I was wrong when I said that the SQL ran fine because I forgot that I added the "Investigator" condition. So, after adding your line with the IIF statement AND adding double quotes around ""Investigator"", now it runs fine.

I guess when I tried the double quotes before (mentioned in my original post), it was after I had added that extra condition. So I was actually getting tripped up by having a second error in the same string.

Thanks for helping to break down that wall I was banging my head against!

Marvin M

 

[jdraw]

Glad to help.