Literal characters in a string

G

Guest

I have a form that I am using to gather user information. But the info I am
trying to gather might contain characters such as the double-quotes (inches
in this instance) and the pound sign (#). Even though I used actual
double-quotes in my SQL statement (""" kind of thing) whenever a user enters
a double-quote or a pound sign in the form it causes an erro in the execution
of the SQL statement. How can I input these kind of characters into a Table?
Is it even possible?

Below is an example of an Insert statement that doesn't work:

INSERT INTO tblJobs
(txtJobName,txtJobDesc,booLip,txtColor,txtPressFinishing,txtPressNote,booMail,booInsert,txtInsertDesc,txtBindNote,txtDestination,dtEntered,intQuantity,intFold,dtCustomer)
VALUES ("Ad Beat - Dallas","56 pg Flexi, 35" 32# Newsprint",False,"4/color on
1, 2, 3 & 6","3/16" head trim, 8.25 x 10.75","Bundle tie and stack on
skid",False,False,"","","Delivery address",#5/8/2007#,15000,1,#5/8/2007#)


In the Values section, note the second entry. It was entered as :

56 pg Flexi, 35" 32# Newsprint

But I don't think it is being interpreted that way. Is it possible to Insert
ANY character that a user has input? If so, how?

Thanks
 
M

Marshall Barton

Steven said:
I have a form that I am using to gather user information. But the info I am
trying to gather might contain characters such as the double-quotes (inches
in this instance) and the pound sign (#). Even though I used actual
double-quotes in my SQL statement (""" kind of thing) whenever a user enters
a double-quote or a pound sign in the form it causes an erro in the execution
of the SQL statement. How can I input these kind of characters into a Table?
Is it even possible?

Below is an example of an Insert statement that doesn't work:

INSERT INTO tblJobs
(txtJobName,txtJobDesc,booLip,txtColor,txtPressFinishing,txtPressNote,booMail,booInsert,txtInsertDesc,txtBindNote,txtDestination,dtEntered,intQuantity,intFold,dtCustomer)
VALUES ("Ad Beat - Dallas","56 pg Flexi, 35" 32# Newsprint",False,"4/color on
1, 2, 3 & 6","3/16" head trim, 8.25 x 10.75","Bundle tie and stack on
skid",False,False,"","","Delivery address",#5/8/2007#,15000,1,#5/8/2007#)


In the Values section, note the second entry. It was entered as :

56 pg Flexi, 35" 32# Newsprint
Click to expand...

All characters except the double quote can be used inside
quotes. To include a quote inside quotes, use two quotes.

"56 pg Flexi, 35"" 32# Newsprint"

Since you are getting the string with the quote from a text
box on a form, use the Replace function to deal with it:

strSQL = "INSERT INTO tblJobs " _
& "(txtJobName, . . . " _
& "VALUES (""" & Replace(JobName, """", """""") _
& """, """ & Replace( . . ., """", """""") _
& """, """ & Replace( . . ., """", """""") _
. . .
& ")"
db.Execute strSQL, dbFailOnError

Be sure to count all those quotes carefully, which is more
that I can do most of the time ;-)
 
Top