473,324 Members | 2,239 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

How to remove aspostophe

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
9 3965
"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
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
"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
"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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

12
by: Sam Collett | last post by:
How do I remove an item with a specified value from an array? i.e. array values 1,2,2,5,7,12,15,21 remove 2 from array would return 1,5,7,12,15,21 (12 and 21 are NOT removed, duplicates are...
11
by: Tony Johansson | last post by:
Hello! I have some problem with STL function remove I have two classes called Handle which is a template class and Integer which is not a template class. The Integer class is just a wrapper...
6
by: Arne Claus | last post by:
Hi If've just read, that remove() on a list does not actually remove the elements, but places them at the end of the list (according to TC++STL by Josuttis). It also says, that remove returns a...
3
by: Don | last post by:
My user control has a combobox with an arraylist attached to it along with custom add and remove methods. The "Add" method is working great. However I don't understand why the "Remove" method...
3
by: Niyazi | last post by:
Hi all, I have a dataTable that contains nearly 38400 rows. In the dataTable consist of 3 column. column 1 Name: MUHNO column 2 Name: HESNO Column 3 Name: BALANCE Let me give you some...
6
by: tshad | last post by:
Is there a reason to use session.remove over session.contents.remove? Don't they both remove the key and data from the contents collection? I assume that session(x) = nothing does essentially...
7
by: Susan Mackay | last post by:
I have a data table that is connected to a database table with a data adapter in the 'standard' manner. However I want to be able to remove selected rows from the data table (i.e. no longer...
6
by: sam_cit | last post by:
Hi Everyone, I'm using remove() function to delete a file, and i observed the following behavior, Concerned file : sample.txt Operation : i open the file in read mode and don't close the...
10
by: =?Utf-8?B?YmJn?= | last post by:
Hi all, I wanted to go through each entry(?) of ArrayList and remove some particular entry. So I tried following but it throws exception at runtime: foreach (myEntry entry in myArrayList) {...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.