Problem with the ' character is SQL INSERT statment

[dafyddg]

Hi all,

Having a rather silly problem with SQL insert in access

Basically i build an SQL insert statment as a string and one the values i put into it is a string e.g.

Dim test as string
Dim SQL as string

test = "Hello"
SQL = "INSERT INTO Testtabel([Astring]) VALUES('" & test & "')

This works fine in most intances however a probelm arises whenever i use the ' character within th string e.g.

test = "don't say hello"

as the ' character appears to be taken to mean the end o thr string withing the SQL statment. It then expect a comma but doesn't find one and so throws a syntax error.

Is there any other way of writing the the SQL line so that i can use a character other than ' to define the bounds of the string or do i have to stop my users from using ' in that paticular field?

Thanks

 

[PHV]

Replace this:
[tt]VALUES('" & test & "')[/tt]
By this:
[tt]VALUES('" & Replace(test, "'", "''") & "')[/tt]

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dafyddg]

Cheers for the tip mate, worked a treat