Quote marks in VBA 2

[MikeRBS]

I had a query which worked fine so I've tried to move it into a module but the IDE complains. It says something like "expected end of statement" and highights the bold bit below.

DoCmd.RunSQL "INSERT INTO Inv_StaffList ( Surname, Forename)" & _
"SELECT Mid([DEV_CONTACT],(InStr(1,[DEV_CONTACT]," ")+1),((Len([DEV_CONTACT]))-InStr(1,[DEV_CONTACT]," ")))," & _
"Left([DEV_CONTACT],(InStr(1,[DEV_CONTACT]," ")-1))," & _
"FROM Applications",-1

My guess is it is having a problem with a quote appearing within a quoted string. How do you get around that?

 

[oharab]

If you need a quote in a string you need to insert 2 quotes:

DoCmd.RunSQL "INSERT INTO Inv_StaffList ( Surname, Forename)" & _
"SELECT  Mid([DEV_CONTACT],(InStr(1,[DEV_CONTACT],[COLOR=red]"[/color]" "[COLOR=red]"[/color])+1),((Len([DEV_CONTACT]))-InStr(1,[DEV_CONTACT],[COLOR=red]"[/color]" "[COLOR=red]"[/color])))," & _
        "Left([DEV_CONTACT],(InStr(1,[DEV_CONTACT],[COLOR=red]"[/color]" "[COLOR=red]"[/color])-1))," & _
"FROM Applications",-1

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"

NoFX-The Decline
----------------------------------------------

 

[MikeRBS]

Ben

You're a star.

Hence the star.

Many thanks

Mike

 

[VictoryHighway]

Thanks for this tip! I have traditionally always used CHR$(34) to represent a double-quote. I will have to try your method!