FindFirst and quotation marks?

[Tom]

Hi

I have a problem with the FindFirst-method. I want to search the following
article:

WOLF BLASS "YELLOW LABEL"-01

This article contains quotation marks and that's the reason, why the
FindFirst mrthod fails. How to get this work? Any ideas?

Tom

 

[Ken Snell [MVP]]

..FindFirst "[FieldName] = 'WOLF BLASS ""YELLOW LABEL""-01'"

Double up the " characters in the text string. If you're using a variable or
control as the source of the string:

..FindFirst "[FieldName] = '" & Replace(VariableName, Chr(34), Chr(34) &
Chr(34), 1, -1, 1) & "'"

 

[Tom]

Ken

Thank you for your answer. I entered the following code:

If InStr(strCriteria, "'") = 0 Then
rst.FindFirst "Product='" & strCriteria & "'"
Else
rst.FindFirst "Product=""" & strCriteria & """"
End If

But how to solve this, when the product contains an apostrophe AND a
quotation mark?

Tom

.FindFirst "[FieldName] = 'WOLF BLASS ""YELLOW LABEL""-01'"

Double up the " characters in the text string. If you're using a variable or
control as the source of the string:

.FindFirst "[FieldName] = '" & Replace(VariableName, Chr(34), Chr(34) &
Chr(34), 1, -1, 1) & "'"

--

Ken Snell
<MS ACCESS MVP>

Hi

I have a problem with the FindFirst-method. I want to search the following
article:

WOLF BLASS "YELLOW LABEL"-01

This article contains quotation marks and that's the reason, why the
FindFirst mrthod fails. How to get this work? Any ideas?

Tom

 

[Roger Carlson]

You could use the Replace function to replace one with the other in the
criteria string. For instance:

rst.FindFirst "Product='" & replace(strCriteria,"'", chr(34) )& "'"
or maybe
rst.FindFirst "Product='" & replace(strCriteria,"'", """" )& "'"

Which *should* replace all the apostophes with quotes. (I'm going from
memory here, so test please). Then you only need one version of the
FindFirst. This doesn't actually change the value of the field, just your
comparison string.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Ken

Thank you for your answer. I entered the following code:

If InStr(strCriteria, "'") = 0 Then
rst.FindFirst "Product='" & strCriteria & "'"
Else
rst.FindFirst "Product=""" & strCriteria & """"
End If

But how to solve this, when the product contains an apostrophe AND a
quotation mark?

Tom

.FindFirst "[FieldName] = 'WOLF BLASS ""YELLOW LABEL""-01'"

Double up the " characters in the text string. If you're using a

variable

or
control as the source of the string:

.FindFirst "[FieldName] = '" & Replace(VariableName, Chr(34), Chr(34) &
Chr(34), 1, -1, 1) & "'"

--

Ken Snell
<MS ACCESS MVP>

Hi

I have a problem with the FindFirst-method. I want to search the following
article:

WOLF BLASS "YELLOW LABEL"-01

This article contains quotation marks and that's the reason, why the
FindFirst mrthod fails. How to get this work? Any ideas?

Tom

 

[Tom]

Roger,

I made the following test:

Sub Test()
Dim rs As DAO.Recordset
Dim strCriteria(1 To 3) As String
Dim i As Integer

Set rs = CurrentDb.OpenRecordset("Data", dbOpenDynaset)

strCriteria(1) = "'apostrophe'"
strCriteria(2) = """quotation mark"""
strCriteria(3) = "'apostrophe' ""quotation mark"""

For i = 1 To 3
rs.FindFirst "Product='" & Replace(strCriteria(i), "'", """") & "'"
Debug.Print i & "; Found: " & Not rs.NoMatch
Next i

End Sub

All criterias are stored within the recordset, but only the 2nd criteria is
found. What am I doing wrong?

Tom

You could use the Replace function to replace one with the other in the
criteria string. For instance:

rst.FindFirst "Product='" & replace(strCriteria,"'", chr(34) )& "'"
or maybe
rst.FindFirst "Product='" & replace(strCriteria,"'", """" )& "'"

Which *should* replace all the apostophes with quotes. (I'm going from
memory here, so test please). Then you only need one version of the
FindFirst. This doesn't actually change the value of the field, just your
comparison string.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Ken

Thank you for your answer. I entered the following code:

If InStr(strCriteria, "'") = 0 Then
rst.FindFirst "Product='" & strCriteria & "'"
Else
rst.FindFirst "Product=""" & strCriteria & """"
End If

But how to solve this, when the product contains an apostrophe AND a
quotation mark?

Tom

.FindFirst "[FieldName] = 'WOLF BLASS ""YELLOW LABEL""-01'"

Double up the " characters in the text string. If you're using a

variable

or
control as the source of the string:

.FindFirst "[FieldName] = '" & Replace(VariableName, Chr(34), Chr(34) &
Chr(34), 1, -1, 1) & "'"

--

Ken Snell
<MS ACCESS MVP>

Hi

I have a problem with the FindFirst-method. I want to search the following
article:

WOLF BLASS "YELLOW LABEL"-01

This article contains quotation marks and that's the reason, why the
FindFirst mrthod fails. How to get this work? Any ideas?

Tom

 

[Roger Carlson]

I'm sorry. You aren't doing anything wrong. I just gave stupid advice and
cost you productive time chasing a wild goose. Of course the Replace works
fine, the criteria just no longer matches the field, so of course it won't
find a match. On the bright side, it doesn't crash! <grimace>

Again, I'm terribly sorry for wasting your time.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org


Off hand, I don't see a way to find a string with BOTH a quote and
apostrophe in it.

Roger,

I made the following test:

Sub Test()
Dim rs As DAO.Recordset
Dim strCriteria(1 To 3) As String
Dim i As Integer

Set rs = CurrentDb.OpenRecordset("Data", dbOpenDynaset)

strCriteria(1) = "'apostrophe'"
strCriteria(2) = """quotation mark"""
strCriteria(3) = "'apostrophe' ""quotation mark"""

For i = 1 To 3
rs.FindFirst "Product='" & Replace(strCriteria(i), "'", """") & "'"
Debug.Print i & "; Found: " & Not rs.NoMatch
Next i

End Sub

All criterias are stored within the recordset, but only the 2nd criteria is
found. What am I doing wrong?

Tom

You could use the Replace function to replace one with the other in the
criteria string. For instance:

rst.FindFirst "Product='" & replace(strCriteria,"'", chr(34) )& "'"
or maybe
rst.FindFirst "Product='" & replace(strCriteria,"'", """" )& "'"

Which *should* replace all the apostophes with quotes. (I'm going from
memory here, so test please). Then you only need one version of the
FindFirst. This doesn't actually change the value of the field, just your
comparison string.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Ken

Thank you for your answer. I entered the following code:

If InStr(strCriteria, "'") = 0 Then
rst.FindFirst "Product='" & strCriteria & "'"
Else
rst.FindFirst "Product=""" & strCriteria & """"
End If

But how to solve this, when the product contains an apostrophe AND a
quotation mark?

Tom



.FindFirst "[FieldName] = 'WOLF BLASS ""YELLOW LABEL""-01'"

Double up the " characters in the text string. If you're using a variable
or
control as the source of the string:

.FindFirst "[FieldName] = '" & Replace(VariableName, Chr(34),

Chr(34)

&

 

[Ken Snell [MVP]]

Your code is overly long. Try this:

rst.FindFirst "Product='" & Replace(Replace(strCriteria, Chr(34), _
Chr(34) & Chr(34), 1, -1, 1), "'", "''", 1, -1, 1)) & "'"

You can nest the Replace function to replace both characters in a single
step.

--

Ken Snell
<MS ACCESS MVP>

Ken

Thank you for your answer. I entered the following code:

If InStr(strCriteria, "'") = 0 Then
rst.FindFirst "Product='" & strCriteria & "'"
Else
rst.FindFirst "Product=""" & strCriteria & """"
End If

But how to solve this, when the product contains an apostrophe AND a
quotation mark?

Tom

.FindFirst "[FieldName] = 'WOLF BLASS ""YELLOW LABEL""-01'"

Double up the " characters in the text string. If you're using a

variable

or
control as the source of the string:

.FindFirst "[FieldName] = '" & Replace(VariableName, Chr(34), Chr(34) &
Chr(34), 1, -1, 1) & "'"

--

Ken Snell
<MS ACCESS MVP>

Hi

I have a problem with the FindFirst-method. I want to search the following
article:

WOLF BLASS "YELLOW LABEL"-01

This article contains quotation marks and that's the reason, why the
FindFirst mrthod fails. How to get this work? Any ideas?

Tom

 

[Roger Carlson]

It's funny how the subconcious works. This just came to me this morning:

'***********************
Sub test()
Dim rs As DAO.Recordset
Dim strCriteria(1 To 3) As String
Dim i As Integer

Set rs = CurrentDb.OpenRecordset("Data", dbOpenDynaset)

strCriteria(1) = "'apostrophe'"
strCriteria(2) = """quotation mark"""
strCriteria(3) = "'apostrophe' ""quotation mark"""

For i = 1 To 3
rs.FindFirst "MyReplace([Product],""'"", """""""")='" &
Replace(strCriteria(i), "'", """") & "'"
Debug.Print i & strCriteria(i) & "; Found: " & Not rs.NoMatch
Next i

End Sub

'***********************
Function MyReplace(MyString As String, ToReplace As String, ReplaceWith As
String) As String
MyReplace = Replace(MyString, ToReplace, ReplaceWith)
End Function
'***********************

I had to create my own Replace function (MyReplace) because the normal it
could not find the normal Replace function.

Give that a try.


--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Roger,

I made the following test:

Sub Test()
Dim rs As DAO.Recordset
Dim strCriteria(1 To 3) As String
Dim i As Integer

Set rs = CurrentDb.OpenRecordset("Data", dbOpenDynaset)

strCriteria(1) = "'apostrophe'"
strCriteria(2) = """quotation mark"""
strCriteria(3) = "'apostrophe' ""quotation mark"""

For i = 1 To 3
rs.FindFirst "Product='" & Replace(strCriteria(i), "'", """") & "'"
Debug.Print i & "; Found: " & Not rs.NoMatch
Next i

End Sub

All criterias are stored within the recordset, but only the 2nd criteria is
found. What am I doing wrong?

Tom

You could use the Replace function to replace one with the other in the
criteria string. For instance:

rst.FindFirst "Product='" & replace(strCriteria,"'", chr(34) )& "'"
or maybe
rst.FindFirst "Product='" & replace(strCriteria,"'", """" )& "'"

Which *should* replace all the apostophes with quotes. (I'm going from
memory here, so test please). Then you only need one version of the
FindFirst. This doesn't actually change the value of the field, just your
comparison string.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Ken

Thank you for your answer. I entered the following code:

If InStr(strCriteria, "'") = 0 Then
rst.FindFirst "Product='" & strCriteria & "'"
Else
rst.FindFirst "Product=""" & strCriteria & """"
End If

But how to solve this, when the product contains an apostrophe AND a
quotation mark?

Tom



.FindFirst "[FieldName] = 'WOLF BLASS ""YELLOW LABEL""-01'"

Double up the " characters in the text string. If you're using a variable
or
control as the source of the string:

.FindFirst "[FieldName] = '" & Replace(VariableName, Chr(34),

Chr(34)

&

 

[Tom]

Hi

Your advices does not solve the problem. The following code works fine with
products, that contains an apostrophe or a quotation mark. But I don't know
how to solve it with products that contain an apostrophe AND a quotation
mark.

Tom


Sub Test()
Dim rst As DAO.Recordset
Dim strCriteria(1 To 3) As String
Dim i As Integer

Set rst = CurrentDb.OpenRecordset("Data", dbOpenDynaset)

strCriteria(1) = "'apostrophe'"
strCriteria(2) = """quotation mark"""
strCriteria(3) = "'apostrophe' AND ""quotation mark"""

For i = 1 To 3
If InStr(strCriteria(i), "'") > 0 And InStr(strCriteria(i), """") >
0 Then
'how to solve this?
rst.FindFirst "Product='" & strCriteria(i) & "'"

ElseIf InStr(strCriteria(i), "'") = 0 Then
rst.FindFirst "Product='" & strCriteria(i) & "'"

Else
rst.FindFirst "Product=""" & strCriteria(i) & """"

End If

Debug.Print i & "; Found: " & Not rst.NoMatch
Next i

End Sub

 

[Ken Snell [MVP]]

see my reply of 18 August (2:45 pm Eastern Daylight Time zone).

 

[Tom]

Ken,

Ok, I changed the code as follows:

Sub Test()
Dim rst As DAO.Recordset
Dim strCriteria(1 To 3) As String
Dim i As Integer

Set rst = CurrentDb.OpenRecordset("Data", dbOpenDynaset)

strCriteria(1) = "'apostrophe'"
strCriteria(2) = """quotation mark"""
strCriteria(3) = "'apostrophe' AND ""quotation mark"""

For i = 1 To 3
rst.FindFirst "product='" & Replace(Replace(strCriteria(i), Chr(34),
_
Chr(34) & Chr(34), 1, -1, 1), "'", "''", 1, -1, 1) & "'"
Debug.Print i & "; Found: " & Not rst.NoMatch
Next i

End Sub


Your code doesn't seem to work. Only the first product is found, the other
products not.... What am I doing wrong?

Tom

 

[Ken Snell [MVP]]

I didn't suggest the For loop. Here is what I'm suggesting:

rst.FindFirst "Product='" & Replace(Replace(strCriteria, Chr(34), _
Chr(34) & Chr(34), 1, -1, 1), "'", "''", 1, -1, 1)) & "'"

No loop should be needed.

 

[Tom]

you code works with this value:

'apostrophe'

but fails with the following values:

"quotation mark"
'apostrophe' AND "quotation mark"


Tom

 

[Tom]

ok, I solved it. here is the code:

Sub Test()
Dim rst As DAO.Recordset
Dim strCriteria(1 To 3) As String
Dim i As Integer

Set rst = CurrentDb.OpenRecordset("Data", dbOpenDynaset)

strCriteria(1) = "'apostrophe'"
strCriteria(2) = """quotation mark"""
strCriteria(3) = "'apostrophe' AND ""quotation mark"""

For i = 1 To 3
rst.FindFirst "Product='" & Replace(strCriteria(i), "'", "''",
1, -1, 1) & "'"
Debug.Print i & "; Found: " & Not rst.NoMatch
Next i

End Sub