This code is actually in Excel, and I have one specific question. This is
code that enters the value myStr into an Access table called MYTABLE

For i = 1 To 5
myStr = cells(i,1).value
Sql = "INSERT INTO MYTABLE VALUES (" & Chr(39) & myStr & Chr(39) & ")"
Connection.Execute Sql
Next i

This works (it's part of a larger procedure), unless the value myStr has an
apostrophe in it - can you tell me how to upload a value with an apostrophe
in it?

thanks
Daniel Bonallack

Chr(39) is an aposophe. Any apostophe in the string has to be replace wit
two aposttophes


For i = 1 To 5
myStr = cells(i,1).value
mystr = replace(mystr,chr(39),chr(39) & chr(39))
Sql = "INSERT INTO MYTABLE VALUES (" & Chr(39) & myStr & Chr(39) & ")"
Connection.Execute Sql
Next i

instead your can use
mystr = replace(mystr,"'","''")




"Daniel Bonallack" wrote:

> This code is actually in Excel, and I have one specific question. This is
> code that enters the value myStr into an Access table called MYTABLE
>
> For i = 1 To 5
> myStr = cells(i,1).value
> Sql = "INSERT INTO MYTABLE VALUES (" & Chr(39) & myStr & Chr(39) & ")"
> Connection.Execute Sql
> Next i
>
> This works (it's part of a larger procedure), unless the value myStr has an
> apostrophe in it - can you tell me how to upload a value with an apostrophe
> in it?
>
> thanks
> Daniel Bonallack
>

Thanks - that works perfectly

"joel" wrote:

> Chr(39) is an aposophe. Any apostophe in the string has to be replace wit
> two aposttophes
>
>
> For i = 1 To 5
> myStr = cells(i,1).value
> mystr = replace(mystr,chr(39),chr(39) & chr(39))
> Sql = "INSERT INTO MYTABLE VALUES (" & Chr(39) & myStr & Chr(39) & ")"
> Connection.Execute Sql
> Next i
>
> instead your can use
> mystr = replace(mystr,"'","''")
>
>
>
>
> "Daniel Bonallack" wrote:
>
> > This code is actually in Excel, and I have one specific question. This is
> > code that enters the value myStr into an Access table called MYTABLE
> >
> > For i = 1 To 5
> > myStr = cells(i,1).value
> > Sql = "INSERT INTO MYTABLE VALUES (" & Chr(39) & myStr & Chr(39) & ")"
> > Connection.Execute Sql
> > Next i
> >
> > This works (it's part of a larger procedure), unless the value myStr has an
> > apostrophe in it - can you tell me how to upload a value with an apostrophe
> > in it?
> >
> > thanks
> > Daniel Bonallack
> >