Apostrophe Messing Up SQL String

[Ron]

I am using the following code:

strSQL = "INSERT into InvoiceDetail(InvoiceRecno, RXNbr, QtyFilled,
Description, Filldate, Amount, Source ) " & _
"Values(" & InvoiceID & ",'" & RXNbr & "'," & quantity & ",'" & Description
& "',#" & Filldate & "#," & copay & ",'B')"

db.Execute strSQL, dbFailOnError

The problem I'm encountering is that I just found out that users are
sometimes entering apostrophes in the description data, so the Execute
statement is misinterpreting the SQL String. Is there a to build the SQL
string so that the Description field will be dropped in literally?

 

[RoyVidar]

I am using the following code:

strSQL = "INSERT into InvoiceDetail(InvoiceRecno, RXNbr, QtyFilled,
Description, Filldate, Amount, Source ) " & _
"Values(" & InvoiceID & ",'" & RXNbr & "'," & quantity & ",'" & Description
& "',#" & Filldate & "#," & copay & ",'B')"

db.Execute strSQL, dbFailOnError

The problem I'm encountering is that I just found out that users are
sometimes entering apostrophes in the description data, so the Execute
statement is misinterpreting the SQL String. Is there a to build the SQL
string so that the Description field will be dropped in literally?

To solve the single quote, you might try something like this

....",'" & Replace(Description, "'", "''") & "',#" ...

which should make Jet understand it.

 

[Ron]

Thanks Roy! Did the trick!

To solve the single quote, you might try something like this

...",'" & Replace(Description, "'", "''") & "',#" ...

which should make Jet understand it.