By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,327 Members | 2,627 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,327 IT Pros & Developers. It's quick & easy.

How to remove aspostophe

P: n/a
Hi,

Can someone tell me how to remove aspostophe (') from user input.

I don't want to give any error message. Just want to remove or change it to
"" or null, such input when it appear anywhere on a specific field. e.g. if
user input abcd's. I want the final data on the database be look like abcds
or abcd s

MSAccess 2000

Anyone any idea ?

Many thanks in advance.

May 17 '07 #1
Share this Question
Share on Google+
9 Replies


P: n/a
"Smiley" <fi*********@googlemail.comwrote in message
news:f2*******************@news.demon.co.uk...
Hi,

Can someone tell me how to remove aspostophe (') from user input.

I don't want to give any error message. Just want to remove or change it
to "" or null, such input when it appear anywhere on a specific field.
e.g. if user input abcd's. I want the final data on the database be look
like abcds or abcd s

MSAccess 2000

Anyone any idea ?

Many thanks in advance.
There may well be a more elegant approach but this works for me.

*** CODE BEGINS ***

Sub libRemoveCharacter(strSearchChar As String, strField As String, strTable
As String)

'Author: Keith Wilby
'Date: 27 May 2005
'Purpose: remove characters from fields

Dim db As DAO.Database, rs As DAO.Recordset, strSQL As String, intPosition
As Integer
Dim strSearchString As String, strNewString As String
Set db = CurrentDb

strSQL = "Select " & strField & " From " & strTable & " Where InStr(nz([" &
strField & "]),""" & strSearchChar & """)>0"
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount = 0 Then GoTo ExitSub

With rs
.MoveFirst
Do Until .EOF
strSearchString = rs(strField)
intPosition = InStr(1, strSearchString, strSearchChar, 1) - 1 'Count
the characters before the offending character
strNewString = Left(strSearchString, intPosition) 'Add the
characters before the offending character
strNewString = strNewString _
& Right(strSearchString, Len(strSearchString) - (intPosition + 1))
'Add the characters after the offending character
.Edit
rs(strField) = strNewString
.Update
'Debug.Print strNewString
intRecordCount = intRecordCount + 1 'Increment the count
.MoveNext
Loop
End With

ExitSub:
rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

*** CODE ENDS ***

Call the sub from your form's After Update event and pass the search
character, field name and table name to it (Chr(39) represents the
apostrophe but you can pass any character to it):

Call libRemoveCharacter(Chr(39), "MyField", "tblMyTable")

I've never used it from a form's update event but I imagine it'll work OK.

HTH - Keith.
www.keithwilby.com

May 17 '07 #2

P: n/a
Smiley wrote:
Hi,

Can someone tell me how to remove aspostophe (') from user input.

I don't want to give any error message. Just want to remove or change it to
"" or null, such input when it appear anywhere on a specific field. e.g. if
user input abcd's. I want the final data on the database be look like abcds
or abcd s

I need to do this all the time, though in my case, I replace the ' with
'' for Oracle data entry.

Anyway, use the after update event of the text box control into which
the user is data entering.

Private Sub MyTextBox_AfterUpdate()
Replace Me.MyTextBox, "'", ""
End Sub

The above will remove the apostrophe after the user leaves the text box
(to go to the next data entry control, or to click an OK button, etc).

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me
May 17 '07 #3

P: n/a
"Tim Marshall" <TI****@PurplePandaChasers.Moertheriumwrote in message
news:f2**********@coranto.ucs.mun.ca...
>
I need to do this all the time, though in my case, I replace the ' with ''
for Oracle data entry.

Anyway, use the after update event of the text box control into which the
user is data entering.

Private Sub MyTextBox_AfterUpdate()
Replace Me.MyTextBox, "'", ""
End Sub

The above will remove the apostrophe after the user leaves the text box
(to go to the next data entry control, or to click an OK button, etc).
Blimey Tim, that *is* simple. I've never come across the replace function
in VBA, there's always something new to learn. The function I posted is
still good for me because it's to replace characters in an Oracle database
with an HTML GUI.

Tip duly noted, thanks :-)

Keith.

May 17 '07 #4

P: n/a
"Tim Marshall" <TI****@PurplePandaChasers.Moertheriumwrote in message
news:f2**********@coranto.ucs.mun.ca...
>
Private Sub MyTextBox_AfterUpdate()
Replace Me.MyTextBox, "'", ""
End Sub
I've even been able to replace the middle chunk of my code with this
simplified version:

Do Until .EOF
strNewString = rs(strField)
strNewString = Replace(strNewString, strSearchChar, strReplaceChar)
.Edit
rs(strField) = strNewString
.Update
.MoveNext
Loop

Thanks again Tim.

Keith.

May 17 '07 #5

P: n/a
Why do you want to suppress the apostrophe? This places restrictions
on the user and creates artificial use of names etc. If it is because
it causes problems in submitting a search string this is easily
overcome. Microsoft databases can handle apostrophes, not sure about
others such as Oracle...

Most MS products interpret ' ' (that is two apostrophes one following
the other with no space) within a literal experssion as literally an
apostrophe. So the 'fred''s bear' will be the string fred's bear.
Instead of stripping it out add another apostrophe beside it.

This solution works very well and is extensive across Microsoft
products. For example to put an ampersand in a label or button use a
double ampersand eg Wine && Beer will become Wine & Beer in a label
whereas Wine & Beer in a label becomes wine_beer. A single ampersand
is interpreted as the action letter whereas && is interpreted
literally as ampersand.

Another way to overcome it is to use ascii 39 when building the string
for the search eg strSearch = "Fred" &asc(39) &"s Bear"
HTH
td
=================================================

On May 17, 9:17 pm, "Smiley" <firework...@googlemail.comwrote:
Hi,

Can someone tell me how to remove aspostophe (') from user input.

I don't want to give any error message. Just want to remove or change it to
"" or null, such input when it appear anywhere on a specific field. e.g. if
user input abcd's. I want the final data on the database be look like abcds
or abcd s

MSAccess 2000

Anyone any idea ?

Many thanks in advance.

May 17 '07 #6

P: n/a
Hi Tim, Keith,

Thank you so much. I don't know much about MSAccess or VBA and have not used
it for years.

When Tim replied, I couldn't get it work and after Keith's reply, bingo.

You both are life saver.

Have a good weekend.

"Keith Wilby" <he**@there.comwrote in message
news:46**********@glkas0286.greenlnk.net...
"Tim Marshall" <TI****@PurplePandaChasers.Moertheriumwrote in message
news:f2**********@coranto.ucs.mun.ca...
>>
Private Sub MyTextBox_AfterUpdate()
Replace Me.MyTextBox, "'", ""
End Sub

I've even been able to replace the middle chunk of my code with this
simplified version:

Do Until .EOF
strNewString = rs(strField)
strNewString = Replace(strNewString, strSearchChar, strReplaceChar)
.Edit
rs(strField) = strNewString
.Update
.MoveNext
Loop

Thanks again Tim.

Keith.

May 18 '07 #7

P: n/a
Hello TD,

Thank you for your advice. I don't know how to do the coding as such. I used
query to do the search. The aspostophe will delete the search query hence I
need to remove it when user input

If you don't mind, would you give me some coding as to how I would implement
such checking in a search.

MTIA
<ct***@internode.on.netwrote in message
news:11**********************@h2g2000hsg.googlegro ups.com...
Why do you want to suppress the apostrophe? This places restrictions
on the user and creates artificial use of names etc. If it is because
it causes problems in submitting a search string this is easily
overcome. Microsoft databases can handle apostrophes, not sure about
others such as Oracle...

Most MS products interpret ' ' (that is two apostrophes one following
the other with no space) within a literal experssion as literally an
apostrophe. So the 'fred''s bear' will be the string fred's bear.
Instead of stripping it out add another apostrophe beside it.

This solution works very well and is extensive across Microsoft
products. For example to put an ampersand in a label or button use a
double ampersand eg Wine && Beer will become Wine & Beer in a label
whereas Wine & Beer in a label becomes wine_beer. A single ampersand
is interpreted as the action letter whereas && is interpreted
literally as ampersand.

Another way to overcome it is to use ascii 39 when building the string
for the search eg strSearch = "Fred" &asc(39) &"s Bear"
HTH
td
=================================================

On May 17, 9:17 pm, "Smiley" <firework...@googlemail.comwrote:
>Hi,

Can someone tell me how to remove aspostophe (') from user input.

I don't want to give any error message. Just want to remove or change it
to
"" or null, such input when it appear anywhere on a specific field. e.g.
if
user input abcd's. I want the final data on the database be look like
abcds
or abcd s

MSAccess 2000

Anyone any idea ?

Many thanks in advance.


May 18 '07 #8

P: n/a
Keith Wilby wrote:
Blimey Tim, that *is* simple. I've never come across the replace
function in VBA, there's always something new to learn. The function I
posted is still good for me because it's to replace characters in an
Oracle database with an HTML GUI.
Replace was new with A2000 and for my A97 apps, I used a public function
somewhat similar to yours, I think, that I got off the Access Web.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me
May 20 '07 #9

P: n/a
ct***@internode.on.net wrote:
Most MS products interpret ' ' (that is two apostrophes one following
the other with no space) within a literal experssion as literally an
apostrophe. So the 'fred''s bear' will be the string fred's bear.
Instead of stripping it out add another apostrophe beside it.
FYI, that's the way to do it for Oracle (and probably MS SQL, as well.
In Oracle, the ' is the same as the " in VBA.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me
May 20 '07 #10

This discussion thread is closed

Replies have been disabled for this discussion.