Handling single quotes " ' " in data

[Irwin Williams]

Hi,
I have been storing file locations in a table in access. The actual
location comes from a file object.
Sometimes a file name may have a single quote in it - and this is causing an
error in access. As such, i've been using the pipe character for internal
purposes - replacing the " ' " with " | ". And vice-versa when reporting
the information to users.
Does anyone know a better way of handling this?

/irwin

 

[Douglas J. Steele]

There's no reason not to store the single quotes.

Presumably your SQL statement is something like

strSQL = "INSERT INTO MyTable (MyField) " & _
"VALUES ( ' " & strFilename & " ' )"

(extra spaces added for clarity)

Try either:

strSQL = "INSERT INTO MyTable (MyField) " & _
"VALUES ( " & Chr$(34) & strFilename & Chr$(34) & " )"

or

strSQL = "INSERT INTO MyTable (MyField) " & _
"VALUES ( ' " & Replace(strFilename, " ' ", " ' ' ") & " ' )"

(remove the extra spaces in the parameters in the Replace statement before
running!)

 

[Irwin Williams]

Hey Douglas,
So are you suggesting that i should abandon using the recordset like this
for updating/adding:
Set rsGeneral = CurrentDb().OpenRecordset(strTblName)
rsGeneral.AddNew
With objFile
rsGeneral!fileName = Replace(.Name, "'", "" & "|" & "")
rsGeneral!Location = Replace(.Path, "'", "|", 1, -1, vbTextCompare)
rsGeneral!DateCreated = .DateCreated
rsGeneral!DateLastAccessed = .DateLastAccessed
rsGeneral!DateLastWritten = .DateLastModified
rsGeneral!Media = "Softcopy"
rsGeneral!MediaType = .Type
End With
rsGeneral.Update

/irwin

 

[Douglas J. Steele]

You shouldn't need to change the single quotes at all with that approach.

rsGeneral!fileName = .Name
rsGeneral!Location = .Path

should work without any problem at all. (I just tested, and had no problems)

What error were you experiencing?

 

[Irwin Williams]

Well, I was getting this error:

Syntax error (missing operator) in query expression
'location = 'Z:\Transfer Folder\PEARLS\Telephone Workers' 04.xls''.

- notice that " Telephone Workers' " has the " ' "
/irwin

 

[Douglas J. Steele]

In other words, you have no problem loading it into the table, you just have
a problem querying the table.

Use the Replace function to change all occurrences of ' (single quote) to ''
(two single quotes) in your query.

Take a look at my May, 2004 Access Answers column for Pinnacle Publication's
Smart Access. You can download the column (and its accompanying database)
for free at http://members.rogers.com/douglas.j.steele/SmartAccess.html