How to remove aspostophe

[Smiley]

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.

[Keith Wilby]

"Smiley" <fi*********@go oglemail.comwro te in message
news:f2******** ***********@new s.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 libRemoveCharac ter(strSearchCh ar 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.OpenRecordse t(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(strSearchS tring, intPosition) 'Add the
characters before the offending character
strNewString = strNewString _
& Right(strSearch String, Len(strSearchSt ring) - (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 libRemoveCharac ter(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

[Tim Marshall]

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_After Update()
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

[Keith Wilby]

"Tim Marshall" <TI****@PurpleP andaChasers.Moe rtheriumwrote 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_After Update()
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.

[Keith Wilby]

"Tim Marshall" <TI****@PurpleP andaChasers.Moe rtheriumwrote in message
news:f2******** **@coranto.ucs. mun.ca...

>
Private Sub MyTextBox_After Update()
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(strNewS tring, strSearchChar, strReplaceChar)
.Edit
rs(strField) = strNewString
.Update
.MoveNext
Loop

Thanks again Tim.

Keith.

[ctd99]

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...@go oglemail.comwro te:

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.

[Smiley]

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.com wrote in message
news:46******** **@glkas0286.gr eenlnk.net...

"Tim Marshall" <TI****@PurpleP andaChasers.Moe rtheriumwrote in message
news:f2******** **@coranto.ucs. mun.ca...

>>
Private Sub MyTextBox_After Update()
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(strNewS tring, strSearchChar, strReplaceChar)
.Edit
rs(strField) = strNewString
.Update
.MoveNext
Loop

Thanks again Tim.

Keith.

[Smiley]

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***@internod e.on.netwrote in message
news:11******** **************@ h2g2000hsg.goog legroups.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...@go oglemail.comwro te:

>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.

[Tim Marshall]

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

[Tim Marshall]

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