Remove Non Alpha Numeric

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form that I want customers to enter their company, the only problem
is that my database code breaks everytime they try to enter a company name
for example.....

ma'am restaurant

if they enter apostrophy the code breaks
how can i overcome that in cod.


Thanks in advance
 
Hi, Jorge.
if they enter apostrophy the code breaks
how can i overcome that in cod.
Click to expand...

If you want to avoid apostrophes in the text being used inappropriately in a
SQL statement, then you may use the Replace( ) function to replace a single
quote with two single quotes. For example:

Private Sub UpdateBtn_Click()

On Error GoTo ErrHandler

Dim sqlStmt As String
Dim sReplace As String

sReplace = Replace(Me!txtCoName.Value, "'", "''")

sqlStmt = "UPDATE tblCustomers " & _
"SET CoName = '" & sReplace & "' " & _
"WHERE ID = " & Me!txtID.Value
CurrentDb().Execute sqlStmt, dbFailOnError

Exit Sub

ErrHandler:

MsgBox "Error in UpdateBtn_Click( ) in " & vbCrLf & Me.Name & _
" form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

.. . . where txtCoName is the name of the text box that may contain
apostrophes, tblCustomers is the name of the table to be updated, CoName is
the name of the field that needs the company name, txtCoName is the text box
bound to this field, ID is the primary key, and txtID is the text box bound
to this field.

If you have other problems besides the embedded apostrophes, then please
post your code and we'll try to help.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
Good morning Gunny,

I've run into this with one of my apps and did a similar replace - but this
wasn't "satisfactory" as most of the single quotes were being used for feet,
and changing to 2 single quotes (or apostrophes if you will) changed the
text to read inches. So a specification that should be 110' (110 feet)
became, in the viewable text, 110'' (110 inches).

Being as this app tracks specifications for engineers you can imagine how
bad this was - in the long run I ended up getting rid of the replace
function and instead doing a inStr where if a single OR double quote
appeared, the record was NOT saved and a messagebox would pop up informing
them that they would have to replace the character in order to save the
record. Technically it works for them, but would NOT work in a situation
like Jorge's where it's actually being used as an apostrophe which would not
have a valid replacement. However, the users have periodically asked if I've
found a solution so that they can use ' and ", but I haven't yet had any
success.

Any other ideas?

Thanks,

SusanV
 
Hi, Susan.

Keep what the users see separate from what is being updated. The Replace( )
function should be used just before the update in the VBA procedure, not on
the text box that the users are reading on the form. The two single quotes
are a formatting requirement for processing the record that the users never
need to be concerned about -- as long as you've done your job properly.

And if the text box is bound to the field in the table, there's no need for
formatting. It's saved "as-is."

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.
 
Hey Gunny, thanks for getting back to this.

I'm wondering though about when they go back in to read or modify the record
later? Or for the reports? I suppose i could rerun the Replace in reverse
for the viewable control, and as this *is* a bound control I'd need to
redesign the form and report to make the viewable control unbound and pull
the content after Replace from a hidden bound control.

Huh, interesting - I suppose it's workable, if i get time (Ha!!) I'll give
it a try, see how it works out...

Thanks,

Susan
 
Hi, Susan.

It sounds like you're making it harder than it needs to be. There's no need
for you to translate for the users. The users "see" 110' in the bound text
box because that's how it's stored in the table.

To update the record, you are using a method that requires the full SQL
statement to be within a string. (And there are other methods that don't
require this, such as queries, bound text boxes and Recordsets.) Therefore,
you must use string delimiters for string values (elements) within this
string. Unfortunately, if the element already contains that same delimiter,
then you'll have to format the element so that the element isn't handled as
a string delimiter. Using the Replace( ) function solves this formatting
problem when a string is placed within a string. No such problem arises
when reading a bound field on the form.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.
 
Back
Top