Inserting records with a ' character

[Anja]

Hi everyone,

I have to insert strings in a table column that might contain the '
character.

Right now, I use a Replace function as follows:

obj.supplier = Replace(obj.supplier, " ' ", " '' ")

// Ignore the spaces, I just added it for clarity... So, each ' is
replaced by two of them...

However, this actually does add 2 ' in my record. How should the SQL be
formatted so that I can have a ' character in it?

Thanks,
Anja

 

[Douglas J. Steele]

You shouldn't have a problem with single quotes assigning them to fields
like that. The problem comes when you're using SQL statements to insert
them.

In other words, don't bother with the Replace function.

 

[Anja]

You shouldn't have a problem with single quotes assigning them to fields
like that. The problem comes when you're using SQL statements to insert
them.

In other words, don't bother with the Replace function.

Hi there,

Thanks for the answer. Well, I run into problems when I try to do a
lookup. For example, I have the following:

sql = "Select * from Records_T where TransactionDate = " +
Format(obj.recordDate, "\#yyyy\-mm\-dd\#") + " AND Amount = " +
str(obj.amount) _
+ " AND Memo = " + "'" + obj.memo + "'"

Set rstLookup = CurrentDb().OpenRecordset(sql)

Now, here if the memo field has ' characters. This statement fails.

Is there a way to do this without worrying about the ' character?

Cheers,
Anja

 

[Douglas J. Steele]

Your original example showed you assigning a value to a field in a
recordset. Your second example shows you using SQL. The two cases are
different.

In the SQL, you must use the Replace statement:

sql = "Select * from Records_T where TransactionDate = " & _
Format(obj.recordDate, "\#yyyy\-mm\-dd\#") + " AND Amount = " & _
str(obj.amount) & _
" AND Memo = '" & Replace(obj.memo, "'", "''") & "'"

Set rstLookup = CurrentDb().OpenRecordset(sql)

Alternatively, don't use ' as the delimiter in your SQL:

sql = "Select * from Records_T where TransactionDate = " & _
Format(obj.recordDate, "\#yyyy\-mm\-dd\#") + " AND Amount = " & _
str(obj.amount) & _
" AND Memo = " & Chr$(34) & obj.memo & Chr$(34)

(although then you'll run into problems if memo contains a double quote in
it)