Apostrophy in text

  • Thread starter Thread starter kim gill
  • Start date Start date
K

kim gill

User is trying to enter a name into a text field that has
an apostrophy. System won't let her. What do I need to
do so that this name "O'leary" is accepted.
 
There is likely some code involved that is attempting to execute a SQL
statement that includes the value of the textbox. Because Jet SQL uses
quotes to delimit text literals within the SQL string (SELECT * FROM
SomeTable WHERE SomeTextField = 'Some Literal Text Value') we need a way of
indicating when a quote is to be treated as a literal character, not as a
delimiter. This is done by doubling up the quotes, so that O'Leary ends up
in the SQL string as O''Leary.

If you're using Access 2000 or later, you can use the built in Replace()
function for this ...

strTheName = Replace(Me!txtTheName, "'", "''", 1, -1, vbBinaryCompare)
strSQL = "SELECT * FROM SomeTable WHERE SomeTextField = '" & strTheName &
"'"

See Replace() function in the VBA help file for details.
 
If the system is correcting the value make sure the
AllowAutoCorrect property on the textbox is set to 'NO'.
 
Back
Top