stripping quote characters?

[Steve Leferve]

Hey folks -

In vba, how do I get rid of quote characters in strings? Or at least escape
them automatically for making SQL INSERT statements? I looked in the help
index, but nothing jumped out at me.

Steve

[Terry Kreft]

Look at the Replace function.
--
Terry Kreft
MVP Microsoft Access
"Steve Leferve" <le********@osu.edu> wrote in message
news:c6**********@charm.magnus.acs.ohio-state.edu...

Hey folks -

In vba, how do I get rid of quote characters in strings? Or at least escape them automatically for making SQL INSERT statements? I looked in the help
index, but nothing jumped out at me.

Steve

[Steve Leferve]

"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:sf********************@karoo.co.uk...

Look at the Replace function.

Thanks, Terry. That's just what I needed.

Now I'm having problems replacing the Quote character with an empty string.
Shouldn't the below work? My strings still have extra quote characters.

If IsNull(rst!Comment10) Then
strSQL = strSQL & ""","""
Else
strSQL = strSQL & """" & Replace(rst!Comment10, Chr(34), "") & """,
"
End If

[Terry Kreft]

Yes, it should do, theonly thing I've found is that Replace (in early
versions at least) required the supposedly optional Compare argument.

So try

If IsNull(rst!Comment10) Then
strSQL = strSQL & ""","""
Else
strSQL = strSQL & """" & Replace(rst!Comment10, Chr(34), "",
compare:=vbBinaryCompare) & ""","
End If

Or if you want to get rid of the If statement you could just use

strSQL = strSQL & """" & _
& Replace(rst!Comment10 & "", Chr(34), "",
compare:=vbBinaryCompare) & ""","
--
Terry Kreft
MVP Microsoft Access
"Steve Leferve" <le********@osu.edu> wrote in message
news:c6**********@charm.magnus.acs.ohio-state.edu...

"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:sf********************@karoo.co.uk...

Look at the Replace function.

Thanks, Terry. That's just what I needed.

Now I'm having problems replacing the Quote character with an empty

string. Shouldn't the below work? My strings still have extra quote characters.

If IsNull(rst!Comment10) Then
strSQL = strSQL & ""","""
Else
strSQL = strSQL & """" & Replace(rst!Comment10, Chr(34), "") & """, "
End If