Solved

Single Quote In Key Problem

Posted on 2014-02-20
10
210 Views
Last Modified: 2014-03-17
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
Comment
Question by:Bob_Collison
  • 5
  • 3
10 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 39874371
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
 

Author Comment

by:Bob_Collison
ID: 39874469
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
 

Author Comment

by:Bob_Collison
ID: 39874576
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:Bob_Collison
ID: 39874642
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
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 total points
ID: 39876428
0
 

Author Comment

by:Bob_Collison
ID: 39876772
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39876773
if my suggestions worked, please don't close with your own comment, but with mine
thanks
0
 

Author Comment

by:Bob_Collison
ID: 39879183
Fast response and great solution!
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After several hours of googling I could not gather any information on this topic. There are several ways of controlling the USB port connected to any storage device. The best example of that is by changing the registry value of "HKEY_LOCAL_MACHINE\S…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question