invalid characters in ms access db

[laura2k]

hi,

i am trying to insert the ' character into an ms access database but seem to get an error, what is the way round this and what other characters are not allowed in MS Access?

thanx

[MozMan]

laura-

The single quote is what you normally use to tell Access when you are starting or ending a string of data:

LName = 'Smith'

But, if you have one in the middle of your string, it thinks the string has ended, and then doesn't know what to do with the rest of the data:

LName = 'O'Hare'

The way to handle this is to double up the single quote that you are using as an apostrophe:

LName = 'O''Hare'

And that should work for you.

-Moz

[MozMan]

Also, in case you are using VBScript (ASP) to send this data, you can search and replace the single quote this way:

strLname = replace(strLname, "'", "''")

That will make this: O'Hare
look like this: O''Hare
before you send it to the database.

-Moz.

[laura2k]

thanx, will try that.

[mattglet]

function stringify( value )
stringify = "'" & replace(value, "'", "''") & "'"
end function

"INSERT INTO table (name1, name2) VALUES (" & stringify(var1) & ", " & stringify(var2) & ");"

-Matt