Text Strings that Contain an Apostrophe

  • Thread starter Thread starter CSDunn
  • Start date Start date
C

CSDunn

Hello,
I have a combo box designed to look up records in a subform based on the
selection made in the combo box. The Record Source for the combo box is a
SQL Server 2000 View. There is one bound column for the combo box called
'StudentName', and is a concatenated field comprised of
'LastName','FirstName'. These two fields are both the same datatype,
'nvarchar'.

I have run into a problem when either the 'FirstName' or 'LastName' contains
an apostrophe. Basically, when I make a selection from the combo box, if any
part of the name contains an apostrophe, I get the following message:
*********************
Run-time error '3001':
Arguments are of the wrong type, are our of acceptable range, or are in
conflict with one another.
*********************

The code for the AfterUpdate event of the combo box is as follows:
*******************
Private Sub Combo22_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.Find "[StudentName] = '" & Me![Combo22] & "'"
Me.Bookmark = rs.Bookmark
End Sub
*******************

The error message points to; rs.Find "[StudentName] = '" & Me![Combo22] &
"'"

How can I change the AfterUpdate code to handle 'StudentName' when there is
an apostrophe in either the first or last name of the student?

Thanks for your help!

CSDunn
 
CSDunn,

Try it like this...
"[StudentName] = " & """" & Me![Combo22] & """"
(that's 4 "s)

- Steve Schapel, Microsoft Access MVP
 
Steve,
Thanks for your help, but the suggestion below did not work. After trying
your suggestion, and a few variations of it, any selection from the combo
box resulted in the error I mentioned before. I tried to SET
QUOTED_IDENTIFIER OFF in the View of the main form, but this did not help.

I've been given the go ahead to replace any name with an apostrophe with a
space.

Thanks again!

CSDunn

Steve Schapel said:
CSDunn,

Try it like this...
"[StudentName] = " & """" & Me![Combo22] & """"
(that's 4 "s)

- Steve Schapel, Microsoft Access MVP


Hello,
I have a combo box designed to look up records in a subform based on the
selection made in the combo box. The Record Source for the combo box is a
SQL Server 2000 View. There is one bound column for the combo box called
'StudentName', and is a concatenated field comprised of
'LastName','FirstName'. These two fields are both the same datatype,
'nvarchar'.

I have run into a problem when either the 'FirstName' or 'LastName' contains
an apostrophe. Basically, when I make a selection from the combo box, if any
part of the name contains an apostrophe, I get the following message:
*********************
Run-time error '3001':
Arguments are of the wrong type, are our of acceptable range, or are in
conflict with one another.
*********************

The code for the AfterUpdate event of the combo box is as follows:
*******************
Private Sub Combo22_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.Find "[StudentName] = '" & Me![Combo22] & "'"
Me.Bookmark = rs.Bookmark
End Sub
*******************

The error message points to; rs.Find "[StudentName] = '" & Me![Combo22] &
"'"

How can I change the AfterUpdate code to handle 'StudentName' when there is
an apostrophe in either the first or last name of the student?

Thanks for your help!

CSDunn
Click to expand...
Click to expand...
 
You could use a function to automatically replace the apostrophes with two
apostrophes.

Put this function in a regular module:
Public Function SingleQDouble(ByVal xstrReplaceStringValue) As String
'***THIS FUNCTION CONVERTS ONE SINGLE-QUOTE CHARACTER INTO TWO SINGLE-QUOTE
'***CHARACTERS IN A TEXT STRING.

' xstrReplaceStringValue is string variable that contains the text string
that
' needs to be converted

SingleQDouble = Replace(xstrReplaceStringValue, "'", "''")
End Function


Then change your code to this:
rs.Find "[StudentName] = '" & SingleQDouble(Me![Combo22]) & "'"


You then can use this function anytime to always convert text strings with a
single apostrophe into double ones, thereby properly handling the text
string.
--
Ken Snell
<MS ACCESS MVP>

CSDunn said:
Steve,
Thanks for your help, but the suggestion below did not work. After trying
your suggestion, and a few variations of it, any selection from the combo
box resulted in the error I mentioned before. I tried to SET
QUOTED_IDENTIFIER OFF in the View of the main form, but this did not help.

I've been given the go ahead to replace any name with an apostrophe with a
space.

Thanks again!

CSDunn

Steve Schapel said:
CSDunn,

Try it like this...
"[StudentName] = " & """" & Me![Combo22] & """"
(that's 4 "s)

- Steve Schapel, Microsoft Access MVP


Hello,
I have a combo box designed to look up records in a subform based on the
selection made in the combo box. The Record Source for the combo box is a
SQL Server 2000 View. There is one bound column for the combo box called
'StudentName', and is a concatenated field comprised of
'LastName','FirstName'. These two fields are both the same datatype,
'nvarchar'.

I have run into a problem when either the 'FirstName' or 'LastName' contains
an apostrophe. Basically, when I make a selection from the combo box,
Click to expand...
Click to expand...
if
any
part of the name contains an apostrophe, I get the following message:
*********************
Run-time error '3001':
Arguments are of the wrong type, are our of acceptable range, or are in
conflict with one another.
*********************

The code for the AfterUpdate event of the combo box is as follows:
*******************
Private Sub Combo22_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.Find "[StudentName] = '" & Me![Combo22] & "'"
Me.Bookmark = rs.Bookmark
End Sub
*******************

The error message points to; rs.Find "[StudentName] = '" & Me![Combo22] &
"'"

How can I change the AfterUpdate code to handle 'StudentName' when
Click to expand...
Click to expand...
there
is
Click to expand...
Click to expand...
 
Sorry, CS. The method I suggested works fine with Access, and is
standard procedure. So I am very surprised to learn that it didn't
help in your case. But I am afraid I don't know anything about how
SQL Server handles stuff like this.

- Steve Schapel, Microsoft Access MVP
 
Back
Top