?
Solved

Apostraphy

Posted on 2003-03-13
21
Medium Priority
?
492 Views
Last Modified: 2008-02-01
The following code extracts the non-chosen items from a list into the Excluded items table. It works perfectly for all my lists, apart from one whose entries contain apostraphies such "McDowell's" or "O'Neil".
               
CurrentDb.Execute "INSERT INTO ExcludedTable (Field1,fieldname,FieldEntry) VALUES ('" _
& Me.lstSource.ItemData(x) & "' ,  '" & Me.txtReqField.Value & "','" & Me.lstSource.Column(1, x) & "');"

Can anyone help?

0
Comment
Question by:mksilk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 3
  • +6
21 Comments
 

Author Comment

by:mksilk
ID: 8132625
Some sample data:
Field1     Fieldname     FieldEntry
3     SerAdvID     ABBAWAY PTY LTD
4     SerAdvID     JONES, PETER
5     SerAdvID     ABN AMRO MORGANS LTD
6     SerAdvID     ADVISER INVEST SER
7       SerAdvID        O'Neil <------falls over here
0
 
LVL 6

Expert Comment

by:graham_charles
ID: 8132630
You need to use double-apostrophes; the easiest way to do that is to create a SQLQuote function:

Function sqlQuote(vsInput) As String
   ' / quotes out ' in input
   sqlQuote = Replace(vsInput & "", "'", "''")
 
End Function

Then, in your code:
CurrentDb.Execute "INSERT INTO ExcludedTable (Field1,fieldname,FieldEntry) VALUES ('" _
& SQLQuote(Me.lstSource.ItemData(x)) & "' ,  '" & SQLQuote(Me.txtReqField.Value) & "','" & SQLQuote(Me.lstSource.Column(1, x)) & "');"

Hope that helps,

g.


0
 

Author Comment

by:mksilk
ID: 8132648
Some sample data:
Field1     Fieldname     FieldEntry
3     SerAdvID     ABBAWAY PTY LTD
4     SerAdvID     JONES, PETER
5     SerAdvID     ABN AMRO MORGANS LTD
6     SerAdvID     ADVISER INVEST SER
7       SerAdvID        O'Neil <------falls over here
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:mksilk
ID: 8132716
Works well in Access 2000 however I am using Access 97 and it doesn't like the 'Replace' function. Am I missing a reference or is there a similar function in Acc 97?
0
 

Author Comment

by:mksilk
ID: 8132720
Works well in Access 2000 however I am using Access 97 and it doesn't like the 'Replace' function. Am I missing a reference or is there a similar function in Acc 97?
0
 
LVL 1

Accepted Solution

by:
Ghis68 earned 1000 total points
ID: 8133264
You can create your home function
You may have to change string for variant if you have some null string

Public Function fctReplace(strRepl As String) As String

Dim X As Integer
Dim Lenstr As Integer
Dim strrtn As String

Lenstr = Len(strRepl)

For X = 1 To Lenstr
  If Asc(Mid(strRepl, X, 1)) = 34 Then
    strrtn = strrtn & """"""
  Else
    strrtn = strrtn & Mid(strRepl, X, 1)
  End If
Next

fctReplace = strrtn
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 8133462
Try enclosing fields within square brackets.

CurrentDb.Execute "INSERT INTO ExcludedTable ([Field1],[fieldname],[FieldEntry]) VALUES ('" _
& Me.lstSource.ItemData(x) & "' ,  '" & Me.txtReqField.Value & "','" & Me.lstSource.Column(1, x) & "');"


0
 

Author Comment

by:mksilk
ID: 8133498
Alanwarren - your suggestion doesn't work unfortunately.
0
 
LVL 6

Expert Comment

by:TheAmigo
ID: 8133523
If shanesuebsah read this, he'd probably say:

Use chr(34):

CurrentDb.Execute "INSERT INTO ExcludedTable (Field1,fieldname,FieldEntry) VALUES (" & chr(34)_
& Me.lstSource.ItemData(x) & chr(34) & " ,  " & chr(34) & Me.txtReqField.Value & chr(34) & "," & chr(34) & Me.lstSource.Column(1, x) & chr(34) & ");"
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8133553
I just read it :) Yup, that's what I'd say <g>

You can also use this alternative for the Replace function:
http://www.mvps.org/access/strings/str0004.htm

(note, please don't accept this comment as an answer, I am just clarifying the other posts)
0
 

Author Comment

by:mksilk
ID: 8133649
Alanwarren - your suggestion doesn't work unfortunately.
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 8133695
This works
You are using an aposrophe as a string delimiter
When a string contains astring delimiter, you need to double up the string delimiter.

Dim sField1 As String
Dim sfieldname As String
Dim sFieldEntry As String
Dim sql As String
sField1 = "'" & Replace(Me.lstSource.ItemData(x), "'", "''") & "'"
sfieldname = "'" & Replace(Me.txtReqField.Value, "'", "''") & "'"
sFieldEntry = "'" & Replace(Me.lstSource.Column(1, x), "'", "''") & "'"
sql = "INSERT INTO [Excluded Items] (Field1,fieldname,FieldEntry) "
sql = sql & "VALUES (" & sField1 & "," & sfieldname & "," & sFieldEntry & ")"
CurrentDb.Execute sql



Alan
http://www.cashoz.com
0
 
LVL 6

Expert Comment

by:TheAmigo
ID: 8133699
In the top left of this web page, under your username, is a link for "Reload This Question".  That will refresh the page without posting your last comment again.
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 8133712
For Access 97

Function Replace(pStr As String, pSrch As String, pRep As String) As String 'replaces all occurrence of pSrch (case sensitive) with pRep in pStr 'returns modified string

   Dim StartPos As Integer, LeftPos As Integer, RightPos As Integer
   Dim NewStr As String, LeftStr As String, RightStr As String

   ReplaceStr = pStr                                       'default returns
unmodified
   NewStr = pStr                                           'Modified string
   StartPos = 1                                            'Start search at
1st char
   LeftPos = InStr(StartPos, NewStr, pSrch, 0)             'pos of pSrch in
NewStr (binary)
   Do While LeftPos > 0
      RightPos = LeftPos + Len(pSrch) - 1                 'right pos of
pSrch in NewStr
      LeftStr = Left(NewStr, LeftPos - 1)                 'left of NewStr up
to pSrch
      RightStr = Right(NewStr, Len(NewStr) - RightPos)    'right of pStr
after pSrch
      NewStr = LeftStr & pRep & RightStr                  'modified string
      StartPos = Len(LeftStr) + Len(pRep) + 1             'Start search
after replacement
      LeftPos = InStr(StartPos, NewStr, pSrch, 0)         'pos of pSrch in
NewStr
   Loop
   Replace = NewStr
   Exit Function

End Function



Alan
http://www.cashoz.com 
0
 
LVL 6

Expert Comment

by:TheAmigo
ID: 8133748
Holy cow!  It's getting more complex by the minute... just stick with the simple (and much faster) version that I posted a while back (03/13/2003 07:07PM PST).

P.S. The results of your last paste, alanwarren, have some serious line-wrapping problems.
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 8133836
TheAmigo

The line wrap comes from your email program or the web page Here's a version that should survive even the narrowest email editor:


Function _
Replace(pStr _
As _
String, _
pSrch _
As _
String, _
pRep _
As _
String) _
As _
String
Dim _
StartPos _
As _
Integer, _
LeftPos _
As _
Integer, _
RightPos _
As _
Integer
Dim _
NewStr _
As _
String, _
LeftStr _
As _
String, _
RightStr _
As _
String
Replace _
= _
pStr
NewStr _
= _
pStr
StartPos _
= _
1
LeftPos _
= _
InStr(StartPos, _
NewStr, _
pSrch, _
0)
Do _
While _
LeftPos _
> _
0
RightPos _
= _
LeftPos _
+ _
Len(pSrch) _
- _
1
LeftStr _
= _
Left(NewStr, _
LeftPos _
- _
1)
RightStr _
= _
Right(NewStr, _
Len(NewStr) _
- _
RightPos)
NewStr _
= _
LeftStr _
& _
pRep _
& _
RightStr
StartPos _
= _
Len(LeftStr) _
+ _
Len(pRep) _
+ _
1
LeftPos _
= _
InStr(StartPos, _
NewStr, _
pSrch, _
0)
Loop
Replace _
= _
NewStr
Exit _
Function
End _
Function


Adios Amigo
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 8134014
mksilk
Sorry about the linwraps last time
All jokes aside (must be Friday), this will do what you want.

Paste this function into a module
It is a multi purpose function that emulates the Access2k Replace funcion. Not just apostrophe's


Function ReplaceStr(pStr As String, pSrch As String, pRep As String) As String
'replaces all occurrence of pSrch (case sensitive) with pRep in pStr
'returns modified string

   Dim StartPos As Integer, LeftPos As Integer, RightPos As Integer
   Dim NewStr As String, LeftStr As String, RightStr As String

   ReplaceStr = pStr   'default returns unmodified
   NewStr = pStr       'Modified string
   StartPos = 1        'Start search at
1 st char
   LeftPos = InStr(StartPos, NewStr, pSrch, 0) 'pos of pSrch in
NewStr (binary)
   Do While LeftPos > 0
      RightPos = LeftPos + Len(pSrch) - 1 'right pos of pSrch in NewStr
      LeftStr = Left(NewStr, LeftPos - 1) 'left of NewStr up to pSrch
      RightStr = Right(NewStr, Len(NewStr) - RightPos) 'right of pStr
after pSrch
      NewStr = LeftStr & pRep & RightStr 'modified string
      StartPos = Len(LeftStr) + Len(pRep) + 1 'Start search
after replacement
      LeftPos = InStr(StartPos, NewStr, pSrch, 0) 'pos of pSrch in
NewStr
   Loop
   ReplaceStr = NewStr
   Exit Function

End Function

Step...2

adapt Your code like this:

Private Sub Command4_Click()
Dim x As Integer

'I havn't got a clue what the value of x is,
'I assume your setting it to a loop on listcount.
'So I set x = 1
x = 1


'If Nothing selected exit sub
If IsNull(Me.lstSource.Column(1)) Then: Exit Sub

Dim sField1 As String
Dim sfieldname As String
Dim sFieldEntry As String
Dim sql As String

sField1 = "'" & ReplaceStr(Me.lstSource.ItemData(x), "'", "''") & "'"
sfieldname = "'" & ReplaceStr(Me.txtReqField.Value, "'", "''") & "'"
sFieldEntry = "'" & ReplaceStr(Me.lstSource.Column(1, x), "'", "''") & "'"
sql = "INSERT INTO [Excluded Items] (Field1,fieldname,FieldEntry) "
sql = sql & "VALUES (" & sField1 & "," & sfieldname & "," & sFieldEntry & ")"

CurrentDb.Execute sql

End Sub



I have changed the name of the function to avoid ambiguity when your application is upgraded to win2k


Hope this helps
alan@cashoz.com


0
 

Author Comment

by:mksilk
ID: 8134202
Ghis68's answer seems to work, I will trying it this weekend and get back to you on Monday with my answer. Thanks for the input from all of you.
0
 
LVL 4

Expert Comment

by:Zmey2
ID: 8135460
chr(34) - doesn't help?
0
 
LVL 12

Expert Comment

by:nexusnation
ID: 8785702
Hi mksilk,
This question has been abandoned and needs to be finalized (101 days since last comment).

   You can accept an answer, split the points, or get a refund. Go to
   http://www.cityofangels.com/Experts/Closing.htm for information and options.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

nexusnation
EE Cleanup Volunteer for Microsoft Access
0
 
LVL 18

Expert Comment

by:1William
ID: 8893141
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Accept question, points to Ghis68
Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

1William
EE Cleanup Volunteer
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

764 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