• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 222
  • Last Modified:

Single Quote In Key Problem

Hi Folks,
In MS Access 2003 when I run the code below (part of a big chunk of code) it has a problem with the data in the following field when the field contains a single quote character.  e.g. (')
Dim PARM_ADDRESS_KEY As String

Data Example:
CDNL7M 0P9***5483 WALKER'S LINE**********************BURLINGTON ON L7M 0P9******************* *******************************************************************

   'MsgBox "Step 1110 - Update With Member Home Address Data."
   On Error Resume Next 'Mandatory to stop BOF / EOF Error.
   Dim RS_10_MEMBER_MSTR_1110 As New ADODB.Recordset
   Dim Cmd_MEMBER_HOME_ADDRESS_KEY As String
   Cmd_MEMBER_HOME_ADDRESS_KEY = "SELECT HOME_ADDRESS_KEY, SYS_MEMBER_KEY, SYS_MEMBER_LKUP " & _
                                 "FROM 10_MEMBER_MSTR " & _
                                 "WHERE HOME_ADDRESS_KEY = '" & PARM_ADDRESS_KEY & "';"
   RS_10_MEMBER_MSTR_1110.Open Cmd_MEMBER_HOME_ADDRESS_KEY, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
   RS_10_MEMBER_MSTR_1110.MoveFirst
   While RS_10_MEMBER_MSTR_1110.EOF = False
      RS_03_GEO_ADDRESS_WHERE_USED_DATA_0006.AddNew
      RS_03_GEO_ADDRESS_WHERE_USED_DATA_0006!ADDRESS_KEY = [PARM_ADDRESS_KEY]
      RS_03_GEO_ADDRESS_WHERE_USED_DATA_0006!POSTAL_CODE = [WRK_POSTAL_CODE]
      RS_03_GEO_ADDRESS_WHERE_USED_DATA_0006!ADDRESS_USE_TYPE = "H01"
      RS_03_GEO_ADDRESS_WHERE_USED_DATA_0006!USE_TYPE_SEQ = "A01"
      RS_03_GEO_ADDRESS_WHERE_USED_DATA_0006!USE_SOURCE_KEY = RS_10_MEMBER_MSTR_1110!SYS_MEMBER_KEY
      RS_03_GEO_ADDRESS_WHERE_USED_DATA_0006!USE_SOURCE_CONTEXT = "Member - Home Address 01"
      RS_03_GEO_ADDRESS_WHERE_USED_DATA_0006!USE_SOURCE_INFO = RS_10_MEMBER_MSTR_1110!SYS_MEMBER_LKUP
      RS_03_GEO_ADDRESS_WHERE_USED_DATA_0006!ADDRESS_USE_TABLE = "10_MEMBER_MSTR"
      RS_03_GEO_ADDRESS_WHERE_USED_DATA_0006!ADDRESS_USE_FIELD = "HOME_ADDRESS_KEY"
      RS_03_GEO_ADDRESS_WHERE_USED_DATA_0006!BASE_REC = [WRK_BASE_REC]
      RS_03_GEO_ADDRESS_WHERE_USED_DATA_0006.Update
      [ADDRESS_USE_COUNTER] = [ADDRESS_USE_COUNTER] + 1
      RS_10_MEMBER_MSTR_1110.MoveNext
   Wend
   RS_10_MEMBER_MSTR_1110.Close

How can I eliminate the problem of the single quote being in a field that is used for a keyed lookup?

Thanks.
Bob Collison.
0
Bob Collison
Asked:
Bob Collison
  • 5
  • 3
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you have 3 options:
* use the "other" quotes:
WHERE yourfield = " & chr$(34) & yourstring & chr$(34))

* escape the quotes:
WHERE yourfield = '" & replace(yourstring, "'","''") & "' "

* use a parametrized query, if possible (recommended, actually)
0
 
Bob CollisonSystem ArchitectAuthor Commented:
Hi Angel III,
Thanks for the solution.

I have a problem with understanding syntax so could you please provide the code for my particular case for each of your solutions?

Thanks.
Bob Colison
0
 
Bob CollisonSystem ArchitectAuthor Commented:
Hi Angel III,
I tried the first two solutions with the following code and both didn't solve the problem.
"WHERE HOME_ADDRESS_KEY = " & Chr$(34) & PARM_ADDRESS_KEY & Chr$(34)
"WHERE HOME_ADDRESS_KEY = '" & Replace(PARM_ADDRESS_KEY, "'", "''") & "' "

Have I done something wrong?

I don't have any idea what you mean by * use a parametrized query so will wait for a further update from you.

Thanks.  Bob Collison
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Bob CollisonSystem ArchitectAuthor Commented:
Hi Angel III,

With reference to my last update.  I didn't make the change to all of the occurrences of the code.  I have since done that and both Solutions 1 and 2 work.

Sorry for the misleading update.

I am still interested in the syntax for Solution 3 as you have suggested that it is the best one.

Thanks.  Bob Collison.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
Bob CollisonSystem ArchitectAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for Bob_Collison's comment #a39874642

for the following reason:

Multiple solutions were provided all of which are applicable in different situations.
Quick response and great solutions.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if my suggestions worked, please don't close with your own comment, but with mine
thanks
0
 
Bob CollisonSystem ArchitectAuthor Commented:
Fast response and great solution!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now