Solved

Single Quote In Key Problem

Posted on 2014-02-20
10
209 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

This article describes how to add a user-defined command button to the Windows 7 Explorer toolbar.  In the previous article (http://www.experts-exchange.com/A_2172.html), we saw how to put the Delete button back there where it belongs.  "Delete" is …
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
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…

829 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