By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,658 Members | 1,501 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,658 IT Pros & Developers. It's quick & easy.

FindFirst fails on indexed field

P: n/a
Using DAO 3.6 on an Access 2002 database, I'm getting unexpected
results with the FindFirst method. Here's the simple code to test:

Public Sub FindIt()

Dim db As Database, rs As Recordset
Dim sCriteria As String

Set db = DBEngine.Workspaces(0).Databases(0)
Set rs = db.OpenRecordset("tblTest", dbOpenDynaset)

'sCriteria = "MyField = '24"" Diameter'" '<-- Works
sCriteria = "MyField = ""24"""" Diameter""" '<-- Fails
rs.FindFirst sCriteria

Debug.Print sCriteria
Debug.Print "rs.NoMatch = " & rs.NoMatch

End Sub

And the immediate window output:

MyField = '24" Diameter'
rs.NoMatch = False

MyField = "24"" Diameter"
rs.NoMatch = True

tblTest has fields ID (autonumber) and MyField (text 50):
1 12" Diameter
2 24" Diameter
3 36" Diameter

The second criteria, which uses double-quotes as the delimiter fails
if tblTest.MyField is indexed. But it works successfully if the index
is removed. Can anyone duplicate or explain this unexpected behavior
from FindFirst?

Rick Collard
www.msc-lims.com
Jun 27 '08 #1
Share this Question
Share on Google+
25 Replies


P: n/a
On Wed, 21 May 2008 01:50:27 GMT, no****@nospam.nospam (Rick Collard)
wrote:
>Using DAO 3.6 on an Access 2002 database, I'm getting unexpected
results with the FindFirst method. Here's the simple code to test:

Public Sub FindIt()

Dim db As Database, rs As Recordset
Dim sCriteria As String

Set db = DBEngine.Workspaces(0).Databases(0)
Set rs = db.OpenRecordset("tblTest", dbOpenDynaset)

'sCriteria = "MyField = '24"" Diameter'" '<-- Works
sCriteria = "MyField = ""24"""" Diameter""" '<-- Fails
rs.FindFirst sCriteria

Debug.Print sCriteria
Debug.Print "rs.NoMatch = " & rs.NoMatch

End Sub

And the immediate window output:

MyField = '24" Diameter'
rs.NoMatch = False

MyField = "24"" Diameter"
rs.NoMatch = True

tblTest has fields ID (autonumber) and MyField (text 50):
1 12" Diameter
2 24" Diameter
3 36" Diameter

The second criteria, which uses double-quotes as the delimiter fails
if tblTest.MyField is indexed. But it works successfully if the index
is removed. Can anyone duplicate or explain this unexpected behavior
from FindFirst?

Rick Collard
www.msc-lims.com

Can anyone spare a few minutes to duplicate the problem? TIA

Rick Collard
www.msc-lims.com
Jun 27 '08 #2

P: n/a
Rick Collard wrote:
On Wed, 21 May 2008 01:50:27 GMT, no****@nospam.nospam (Rick Collard)
wrote:

>>Using DAO 3.6 on an Access 2002 database, I'm getting unexpected
results with the FindFirst method. Here's the simple code to test:

Public Sub FindIt()

Dim db As Database, rs As Recordset
Dim sCriteria As String

Set db = DBEngine.Workspaces(0).Databases(0)
Set rs = db.OpenRecordset("tblTest", dbOpenDynaset)

'sCriteria = "MyField = '24"" Diameter'" '<-- Works
sCriteria = "MyField = ""24"""" Diameter""" '<-- Fails
rs.FindFirst sCriteria

Debug.Print sCriteria
Debug.Print "rs.NoMatch = " & rs.NoMatch

End Sub

And the immediate window output:

MyField = '24" Diameter'
rs.NoMatch = False

MyField = "24"" Diameter"
rs.NoMatch = True

tblTest has fields ID (autonumber) and MyField (text 50):
1 12" Diameter
2 24" Diameter
3 36" Diameter

The second criteria, which uses double-quotes as the delimiter fails
if tblTest.MyField is indexed. But it works successfully if the index
is removed. Can anyone duplicate or explain this unexpected behavior
>>from FindFirst?
>>

Rick Collard
www.msc-lims.com

Can anyone spare a few minutes to duplicate the problem? TIA
I tried
sCriteria = "TestFld = '" & "24"" Diameter" & "'"
and it came out False on NoMatch

Get Up and Go
http://www.youtube.com/watch?v=6HxNGyE3zng
>
Rick Collard
www.msc-lims.com
Jun 27 '08 #3

P: n/a
On Fri, 23 May 2008 10:26:32 -0700, Salad <oi*@vinegar.comwrote:
>
I tried
sCriteria = "TestFld = '" & "24"" Diameter" & "'"
and it came out False on NoMatch

Get Up and Go
http://www.youtube.com/watch?v=6HxNGyE3zng
Salad,
Thanks for testing. Yes, that syntax works correctly because the
single-quote delimiter is not in the search string. Can you try the
problem syntax:

sCriteria = "TestFld = ""24"""" Diameter"""

The above is valid syntax but it will fail to find the match if
TestFld is indexed. If NoMatch is True remove the index and see if
NoMatch is now False.
Rick Collard
www.msc-lims.com
Jun 27 '08 #4

P: n/a
Salad wrote:
Rick Collard wrote:
>On Wed, 21 May 2008 01:50:27 GMT, no****@nospam.nospam (Rick Collard)
wrote:

>>Using DAO 3.6 on an Access 2002 database, I'm getting unexpected
results with the FindFirst method. Here's the simple code to test:

Public Sub FindIt()

Dim db As Database, rs As Recordset
Dim sCriteria As String
Set db = DBEngine.Workspaces(0).Databases(0)
Set rs = db.OpenRecordset("tblTest", dbOpenDynaset)
'sCriteria = "MyField = '24"" Diameter'" '<-- Works
sCriteria = "MyField = ""24"""" Diameter""" '<-- Fails
rs.FindFirst sCriteria
Debug.Print sCriteria
Debug.Print "rs.NoMatch = " & rs.NoMatch
End Sub

And the immediate window output:

MyField = '24" Diameter'
rs.NoMatch = False

MyField = "24"" Diameter"
rs.NoMatch = True

tblTest has fields ID (autonumber) and MyField (text 50):
1 12" Diameter
2 24" Diameter
3 36" Diameter

The second criteria, which uses double-quotes as the delimiter fails
if tblTest.MyField is indexed. But it works successfully if the index
is removed. Can anyone duplicate or explain this unexpected behavior

>>from FindFirst?

>>>

Rick Collard
www.msc-lims.com


Can anyone spare a few minutes to duplicate the problem? TIA


I tried
sCriteria = "TestFld = '" & "24"" Diameter" & "'"
and it came out False on NoMatch

Get Up and Go
http://www.youtube.com/watch?v=6HxNGyE3zng
>>
Rick Collard
www.msc-lims.com
On other thing you might want to consider...using the Replace function
to convert " to in.
Jun 27 '08 #5

P: n/a
Rick Collard wrote:
On Fri, 23 May 2008 10:26:32 -0700, Salad <oi*@vinegar.comwrote:

>>I tried
sCriteria = "TestFld = '" & "24"" Diameter" & "'"
and it came out False on NoMatch

Get Up and Go
http://www.youtube.com/watch?v=6HxNGyE3zng


Salad,
Thanks for testing. Yes, that syntax works correctly because the
single-quote delimiter is not in the search string. Can you try the
problem syntax:

sCriteria = "TestFld = ""24"""" Diameter"""

The above is valid syntax but it will fail to find the match if
TestFld is indexed. If NoMatch is True remove the index and see if
NoMatch is now False.
Rick Collard
www.msc-lims.com
I noticed that when I was looked at, ran, your code. What do you want
me to say? That it didn't work? I'm not concerned too much on what
doesn't work. I provided a solution where it would work. If you have a
working solution for a problem why do you want to pursue non-working
solutions?
Jun 27 '08 #6

P: n/a
On Fri, 23 May 2008 15:01:51 -0700, Salad <oi*@vinegar.comwrote:
>Rick Collard wrote:
>On Fri, 23 May 2008 10:26:32 -0700, Salad <oi*@vinegar.comwrote:

>>>I tried
sCriteria = "TestFld = '" & "24"" Diameter" & "'"
and it came out False on NoMatch

Get Up and Go
http://www.youtube.com/watch?v=6HxNGyE3zng


Salad,
Thanks for testing. Yes, that syntax works correctly because the
single-quote delimiter is not in the search string. Can you try the
problem syntax:

sCriteria = "TestFld = ""24"""" Diameter"""

The above is valid syntax but it will fail to find the match if
TestFld is indexed. If NoMatch is True remove the index and see if
NoMatch is now False.
Rick Collard
www.msc-lims.com

I noticed that when I was looked at, ran, your code. What do you want
me to say? That it didn't work? I'm not concerned too much on what
doesn't work. I provided a solution where it would work. If you have a
working solution for a problem why do you want to pursue non-working
solutions?
Salad,
If it failed for you, that's exactly what I wanted to hear. I am just
looking for confirmation that this is a bug and there is indeed a
problem with the FindFirst method. This appears to be a severe
limitation with FindFirst. If the example also failed for you that
would confirm the problem. FindFirst has been an important tool in my
DAO code over the years. I want to know if that tool is no longer
reliable and I thought other Access developers would also.

In the real application I have no control over the contents of the
table field or the search text. My use of a double quote for inches
was purely an example so replacing the double quote is not a solution.
It appears the solution is to not use FindFirst on an indexed text
field if the search text could ever contain either a double or single
quote.

Rick Collard
www.msc-lims.com
Jun 27 '08 #7

P: n/a
Rick Collard wrote:
On Fri, 23 May 2008 15:01:51 -0700, Salad <oi*@vinegar.comwrote:

>>Rick Collard wrote:
>>>On Fri, 23 May 2008 10:26:32 -0700, Salad <oi*@vinegar.comwrote:

I tried
sCriteria = "TestFld = '" & "24"" Diameter" & "'"
and it came out False on NoMatch

Get Up and Go
http://www.youtube.com/watch?v=6HxNGyE3zng

Salad,
Thanks for testing. Yes, that syntax works correctly because the
single-quote delimiter is not in the search string. Can you try the
problem syntax:

sCriteria = "TestFld = ""24"""" Diameter"""

The above is valid syntax but it will fail to find the match if
TestFld is indexed. If NoMatch is True remove the index and see if
NoMatch is now False.
Rick Collard
www.msc-lims.com

I noticed that when I was looked at, ran, your code. What do you want
me to say? That it didn't work? I'm not concerned too much on what
doesn't work. I provided a solution where it would work. If you have a
working solution for a problem why do you want to pursue non-working
solutions?


Salad,
If it failed for you, that's exactly what I wanted to hear. I am just
looking for confirmation that this is a bug and there is indeed a
problem with the FindFirst method. This appears to be a severe
limitation with FindFirst. If the example also failed for you that
would confirm the problem. FindFirst has been an important tool in my
DAO code over the years. I want to know if that tool is no longer
reliable and I thought other Access developers would also.
What is confusing to many, even experienced developers, is the usage of
handling quotes in a string.
>
In the real application I have no control over the contents of the
table field or the search text. My use of a double quote for inches
was purely an example so replacing the double quote is not a solution.
It appears the solution is to not use FindFirst on an indexed text
field if the search text could ever contain either a double or single
quote.

I looked at your original output as well
MyField = '24" Diameter'
rs.NoMatch = False

MyField = "24"" Diameter"
rs.NoMatch = True

I don't see how the above two outputs are equal. The "" after the 24 is
not the same as a ".

I recently had a similar problem. Because MS dropped support for
linking FoxPro files (ODBC being useless) I needed to import files from
a text file, not a dbf file. In my tests the DBF file appended into
Access worked correctly but I got different result from the same data
files as comma delimited. I was scratching my head trying to figure out
what the difference between the two files were. It came down to a text
field with a 24" in it. Thus the import process file processed this one
record incorrectly as it assumed the " was the ending delimiter of the
field and it didn't know how to process the rest of that record. I'm
not so crazy about MS determining our delimiters. For example, the # is
the delimiter for hyperlinks. I'd encounter someone entering #1 in the
display text side and when creating the link
"Display This #1#Http://www.text.com" Access thought 1 was the
hyperlink file name.

I would use what works and move on.

>
Rick Collard
www.msc-lims.com
Jun 27 '08 #8

P: n/a
On Fri, 23 May 2008 19:11:27 -0700, Salad <oi*@vinegar.comwrote:
>Salad,
If it failed for you, that's exactly what I wanted to hear. I am just
looking for confirmation that this is a bug and there is indeed a
problem with the FindFirst method. This appears to be a severe
limitation with FindFirst. If the example also failed for you that
would confirm the problem. FindFirst has been an important tool in my
DAO code over the years. I want to know if that tool is no longer
reliable and I thought other Access developers would also.

What is confusing to many, even experienced developers, is the usage of
handling quotes in a string.
I agree. It is even more confusing when Access handles quotes
differently depending on what part of the product you are using (see
below).
>
>>
In the real application I have no control over the contents of the
table field or the search text. My use of a double quote for inches
was purely an example so replacing the double quote is not a solution.
It appears the solution is to not use FindFirst on an indexed text
field if the search text could ever contain either a double or single
quote.


I looked at your original output as well
MyField = '24" Diameter'
rs.NoMatch = False

MyField = "24"" Diameter"
rs.NoMatch = True

I don't see how the above two outputs are equal. The "" after the 24 is
not the same as a ".
The two search strings above ARE equal. Both are searching for the
text string [24" Diameter] without the brackets. The difference is
the delimiter. The first uses a single quote delimiter, which doesn't
occur in the search string. The second uses the double quote
delimiter and since a double quote does occur in the search string it
must be replaced by back-to-back double quotes so it is not confused
with the delimiter.

My example demonstrates that FindFirst has a serious bug. The query
processor correctly handles the two syntax options. Below is the
equivalent SQL and both correctly retrieve the same table record.

SELECT * FROM tblTest
WHERE MyField = '24" Diameter';

SELECT * FROM tblTest
WHERE MyField = "24"" Diameter";
>I recently had a similar problem. Because MS dropped support for
linking FoxPro files (ODBC being useless) I needed to import files from
a text file, not a dbf file. In my tests the DBF file appended into
Access worked correctly but I got different result from the same data
files as comma delimited. I was scratching my head trying to figure out
what the difference between the two files were. It came down to a text
field with a 24" in it. Thus the import process file processed this one
record incorrectly as it assumed the " was the ending delimiter of the
field and it didn't know how to process the rest of that record. I'm
not so crazy about MS determining our delimiters. For example, the # is
the delimiter for hyperlinks. I'd encounter someone entering #1 in the
display text side and when creating the link
"Display This #1#Http://www.text.com" Access thought 1 was the
hyperlink file name.

I would use what works and move on.
I have concluded that FindFirst is unsafe when used with an indexed
text field. Thanks for the discussion.
>
>>
Rick Collard
www.msc-lims.com
Rick Collard
www.msc-lims.com
Jun 27 '08 #9

P: n/a
Access, of course, is not one technology. It is at least three. I
don't know of any assurance that MS gives that the three technologies
will always behave identically.

Regardless, there are few procedures in any programming/application
development platform that we cannot cause to fail if we are given wide
scope. The failure you describe can be traced back to the original
data structure's non-atomic field where we accept a number, a
qualifier (quotation mark=inches) and a dimension. Clearly, this is
very bad form (Yes, I've noted that this isn't your responsibility;
it's something you've inherited.)

I tend to deny quotes, double of single, in any field. And it's years
and years since I've used FindFirst.
Fr myself, I would (probably) parse the field and create two or three
other fields from it before using it, but I'm not suggesting that you
should.

Regardless, your post alerts us to an anomaly about which we should be
cautious. I duplicated the results last night by changing the
employee, Kotas, to Ko"tas in Northwinds 2007.
I'd prefer to keep the term bug to name something that doesn't work in
normal circumstances, and I don't think those you describe are
entirely normal. Maybe we introduce a "tiny insect" category.

On May 25, 11:30*am, nos...@nospam.nospam (Rick Collard) wrote:
I have concluded that FindFirst is unsafe when used with an indexed
text field. *Thanks for the discussion.
Jun 27 '08 #10

P: n/a
On Sun, 25 May 2008 08:51:59 -0700 (PDT), lyle fairfield
<ly************@gmail.comwrote:
>Access, of course, is not one technology. It is at least three. I
don't know of any assurance that MS gives that the three technologies
will always behave identically.

Regardless, there are few procedures in any programming/application
development platform that we cannot cause to fail if we are given wide
scope. The failure you describe can be traced back to the original
data structure's non-atomic field where we accept a number, a
qualifier (quotation mark=3Dinches) and a dimension. Clearly, this is
very bad form (Yes, I've noted that this isn't your responsibility;
it's something you've inherited.)

I tend to deny quotes, double of single, in any field. And it's years
and years since I've used FindFirst.
Fr myself, I would (probably) parse the field and create two or three
other fields from it before using it, but I'm not suggesting that you
should.

Regardless, your post alerts us to an anomaly about which we should be
cautious. I duplicated the results last night by changing the
employee, Kotas, to Ko"tas in Northwinds 2007.
I'd prefer to keep the term bug to name something that doesn't work in
normal circumstances, and I don't think those you describe are
entirely normal. Maybe we introduce a "tiny insect" category.

On May 25, 11:30=A0am, nos...@nospam.nospam (Rick Collard) wrote:
>I have concluded that FindFirst is unsafe when used with an indexed
text field. =A0Thanks for the discussion.
Thanks for confirming Lyle. Just to clarify, my [24" Diameter] was
fictitious designed only to demonstrate the problem. I suppose a more
real world example would have sufficed:

rs.FindFirst "LastName = ""O'Reilly"""

rs.FindFirst "LastName = 'O''Rielly'"

This first line above works but the second fails to find the record if
it's a dynaset and LastName is indexed. Unfortunately, preventing the
use of any quotes in the field in the application where this problem
surfaced is not an option. I appreciate your time confirming the
problem. There are workarounds but I'll be cautious with FindFirst in
the future.

Rick Collard
www.msc-lims.com
Jun 27 '08 #11

P: n/a
no****@nospam.nospam (Rick Collard) wrote in
news:48****************@news.safepages.com:
I have concluded that FindFirst is unsafe when used with an
indexed text field.
On what grounds? The examples discussed in your post don't
constitute anything wrong with it, seems to me.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jun 27 '08 #12

P: n/a
no****@nospam.nospam (Rick Collard) wrote in
news:48****************@news.safepages.com:
Just to clarify, my [24" Diameter] was
fictitious designed only to demonstrate the problem. I suppose a
more real world example would have sufficed:

rs.FindFirst "LastName = ""O'Reilly"""

rs.FindFirst "LastName = 'O''Rielly'"

This first line above works but the second fails to find the
record if it's a dynaset and LastName is indexed.
Well, of *course* it does, because you're not telling it the same
thing you're telling it with the first example.

I never use literal quotes for this. I always have a global constant
STR_QUOTE that holds Chr(34). I then do this:

rs.FindFirst "LastName = " & STR_QUOTE & "O'Reilly" & STR_QUOTE

The result is that I *always* get correct results.
Unfortunately, preventing the
use of any quotes in the field in the application where this
problem surfaced is not an option. I appreciate your time
confirming the problem. There are workarounds but I'll be
cautious with FindFirst in the future.
You're just not constructing your strings correctly. That is not the
fault of .FindFirst, but your error in telling it to look for the
wrong thing.

If you're really concerned about it, then you should parse your
criteria and replace all quotes with "?" and then use LIKE as your
comparison operator.

But the problem is *not* with .FindFirst. It's clear pilot error on
your part.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jun 27 '08 #13

P: n/a
On 25 May 2008 23:17:52 GMT, "David W. Fenton"
<XX*******@dfenton.com.invalidwrote:
>no****@nospam.nospam (Rick Collard) wrote in
news:48****************@news.safepages.com:
>Just to clarify, my [24" Diameter] was
fictitious designed only to demonstrate the problem. I suppose a
more real world example would have sufficed:

rs.FindFirst "LastName = ""O'Reilly"""

rs.FindFirst "LastName = 'O''Rielly'"

This first line above works but the second fails to find the
record if it's a dynaset and LastName is indexed.

Well, of *course* it does, because you're not telling it the same
thing you're telling it with the first example.
I believe I am telling it the same thing. It's difficult to
distinguish between two single quotes and one double quote. The two
examples just use different delimiters. Here are the equivalent
queries in SQL:

SELECT * FROM tblTest
WHERE LastName = "O'Reilly"

SELECT * FROM tblTest
WHERE LastName = 'O''Reilly'

The second example above uses all single quotes but there are two
back-to-back.
>
I never use literal quotes for this. I always have a global constant
STR_QUOTE that holds Chr(34). I then do this:

rs.FindFirst "LastName = " & STR_QUOTE & "O'Reilly" & STR_QUOTE

The result is that I *always* get correct results.
> Unfortunately, preventing the
use of any quotes in the field in the application where this
problem surfaced is not an option. I appreciate your time
confirming the problem. There are workarounds but I'll be
cautious with FindFirst in the future.

You're just not constructing your strings correctly. That is not the
fault of .FindFirst, but your error in telling it to look for the
wrong thing.
Use the exact WHERE clauses from the SELECT statements above, without
the word WHERE, with FindFirst and one will succeed and the other will
fail. But it will only fail with a dynaset on an indexed field. Drop
the index and it works. Use a snapshot and it will work.
>
If you're really concerned about it, then you should parse your
criteria and replace all quotes with "?" and then use LIKE as your
comparison operator.

But the problem is *not* with .FindFirst. It's clear pilot error on
your part.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Sorry, but this is not pilot error.
Rick Collard
www.msc-lims.com
Jun 27 '08 #14

P: n/a
On 25 May 2008 23:12:35 GMT, "David W. Fenton"
<XX*******@dfenton.com.invalidwrote:
>no****@nospam.nospam (Rick Collard) wrote in
news:48****************@news.safepages.com:
>I have concluded that FindFirst is unsafe when used with an
indexed text field.

On what grounds? The examples discussed in your post don't
constitute anything wrong with it, seems to me.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
The example code in my original post demonstrates the problem. If the
delimiter used with FindFirst, whether a single quote or a double
quote, also occurs in the text string FindFirst will fail if the field
is indexed. Further testing shows the problem is limited to dynasets.
Jet's query processor handles the syntax properly.
Rick Collard
www.msc-lims.com
Jun 27 '08 #15

P: n/a
I initially thought that the problem was in Rick's syntax. I was
wrong.

I wrote and ran this code (in Northwinds 2007 - I expect it would run
OK in previous versions) to test his findings independently.

Unless I am making an error in logic or syntax it shows that Access/
Jet/Ace DOES NOT find (with FindFirst)
"[Last Name] = ""Ko""""tas"""
when an index on [Last Name] exists
but DOES find
"[Last Name] = ""Ko""""tas"""
when an index on [Last Name] does not exist.

It finds
"[Last Name] = 'Ko""tas'"
regardless of the presence of the index.

After some further reflection I think this anomaly could be considered
a small bug; small only because it occurs in the unusual (to me,
anyhow) circumstance of having a double-quote in a text field.

<code>
Sub TestFindFirst()
Dim r As DAO.Recordset
Dim i As DAO.Index
Dim f As DAO.Field

Dim d$
Dim s$

With DBEngine(0)(0)

' be sure we're current
.TableDefs.Refresh
.TableDefs("Employees").Indexes.Refresh

' confirm value of field is Ko"tas
' use syntax in question
.Execute ("UPDATE Employees SET [Last Name] = ""Ko""""tas"" WHERE
ID = 3")
Debug.Print .OpenRecordset("SELECT [Last Name] FROM Employees
WHERE ID = 3")(0)

Set r = .OpenRecordset("Employees", dbOpenDynaset)

' test presence of index "Last name"
On Error Resume Next
Set i = .TableDefs("Employees").Indexes("Last Name")
'On Error GoTo 0
If i Is Nothing Then
d = "Not Indexed"
Else
d = "Indexed"
End If

Debug.Print String(10, "-") & vbNewLine & d

With r
' test single quotes
s = "[Last Name] = 'Ko""tas'"
Debug.Print s
.FindFirst s
Debug.Print "NoMatch = " & .NoMatch

' tets double quotes
.MoveFirst
s = "[Last Name] = ""Ko""""tas"""
Debug.Print s
.FindFirst s
Debug.Print "NoMatch = " & .NoMatch

End With

Set r = Nothing

With .TableDefs("Employees")
.Indexes.Refresh
If i Is Nothing Then
Set i = .CreateIndex("Last Name")
With i
Set f = .CreateField("Last Name", dbText, 50)
.Fields.Append f
End With
.Indexes.Append i
Else
.Indexes.Delete "Last Name"
End If
.Indexes.Refresh
End With

.TableDefs.Refresh
Set i = Nothing

' test presence of index "Last name"
On Error Resume Next
Set i = .TableDefs("Employees").Indexes("Last Name")
On Error GoTo 0
If i Is Nothing Then
d = "Not Indexed"
Else
d = "Indexed"
End If

Debug.Print String(10, "-") & vbNewLine & d

Set r = Nothing
.TableDefs.Refresh
Set r = .OpenRecordset("Employees", dbOpenDynaset)

With r
s = "[Last Name] = 'Ko""tas'"
Debug.Print s
.FindFirst s
Debug.Print "NoMatch = " & .NoMatch

.MoveFirst
s = "[Last Name] = ""Ko""""tas"""
Debug.Print s
.FindFirst s
Debug.Print "NoMatch = " & .NoMatch

End With

Debug.Print String(10, "-")

End With

End Sub
</code>

<immediate window>
Ko"tas
----------
Indexed
[Last Name] = 'Ko"tas'
NoMatch = False
[Last Name] = "Ko""tas"
NoMatch = True
----------
Not Indexed
[Last Name] = 'Ko"tas'
NoMatch = False
[Last Name] = "Ko""tas"
NoMatch = False
----------
</immediate window>



On May 25, 7:17*pm, "David W. Fenton" <XXXuse...@dfenton.com.invalid>
wrote:
nos...@nospam.nospam (Rick Collard) wrote innews:48****************@news.safepages.com:
Just to clarify, my [24" Diameter] was
fictitious designed only to demonstrate the problem. *I suppose a
more real world example would have sufficed:
rs.FindFirst "LastName = ""O'Reilly"""
rs.FindFirst "LastName = 'O''Rielly'"
This first line above works but the second fails to find the
record if it's a dynaset and LastName is indexed.

Well, of *course* it does, because you're not telling it the same
thing you're telling it with the first example.

I never use literal quotes for this. I always have a global constant
STR_QUOTE that holds Chr(34). I then do this:

* rs.FindFirst "LastName = " & STR_QUOTE & "O'Reilly" & STR_QUOTE

The result is that I *always* get correct results.
*Unfortunately, preventing the
use of any quotes in the field in the application where this
problem surfaced is not an option. *I appreciate your time
confirming the problem. *There are workarounds but I'll be
cautious with FindFirst in the future.

You're just not constructing your strings correctly. That is not the
fault of .FindFirst, but your error in telling it to look for the
wrong thing.

If you're really concerned about it, then you should parse your
criteria and replace all quotes with "?" and then use LIKE as your
comparison operator.

But the problem is *not* with .FindFirst. It's clear pilot error on
your part.

--
David W. Fenton * * * * * * * * *http://www.dfenton.com/
usenet at dfenton dot com * *http://www.dfenton.com/DFA/
Jun 27 '08 #16

P: n/a
Someone might notice the omission of a .MoveFirst in the second round
of testing. I've corrected that and it makes no difference to the
results.

On May 26, 8:37*am, lyle fairfield <lyle.fairfi...@gmail.comwrote:
I initially thought that the problem was in Rick's syntax. I was
wrong.

I wrote and ran this code (in Northwinds 2007 - I expect it would run
OK in previous versions) to test his findings independently.

Unless I am making an error in logic or syntax it shows that Access/
Jet/Ace DOES NOT find (with FindFirst)
"[Last Name] = ""Ko""""tas"""
when an index on [Last Name] exists
but DOES find
"[Last Name] = ""Ko""""tas"""
when an index on [Last Name] does not exist.

It finds
"[Last Name] = 'Ko""tas'"
regardless of the presence of the index.

After some further reflection I think this anomaly could be considered
a small bug; small only because it occurs in the unusual (to me,
anyhow) circumstance of having a double-quote in a text field.

<code>
Sub TestFindFirst()
Dim r As DAO.Recordset
Dim i As DAO.Index
Dim f As DAO.Field

Dim d$
Dim s$

With DBEngine(0)(0)

* * ' be sure we're current
* * .TableDefs.Refresh
* * .TableDefs("Employees").Indexes.Refresh

* * ' confirm value of field is Ko"tas
* * ' use syntax in question
* * .Execute ("UPDATE Employees SET [Last Name] = ""Ko""""tas"" WHERE
ID = 3")
* * Debug.Print .OpenRecordset("SELECT [Last Name] FROM Employees
WHERE ID = 3")(0)

* * Set r = .OpenRecordset("Employees", dbOpenDynaset)

* * ' test presence of index "Last name"
* * On Error Resume Next
* * Set i = .TableDefs("Employees").Indexes("Last Name")
* * 'On Error GoTo 0
* * If i Is Nothing Then
* * * * d = "Not Indexed"
* * Else
* * * * d = "Indexed"
* * End If

* * Debug.Print String(10, "-") & vbNewLine & d

* * With r
* * * * ' test single quotes
* * * * s = "[Last Name] = 'Ko""tas'"
* * * * Debug.Print s
* * * * .FindFirst s
* * * * Debug.Print "NoMatch = " & .NoMatch

* * * * ' tets double quotes
* * * * .MoveFirst
* * * * s = "[Last Name] = ""Ko""""tas"""
* * * * Debug.Print s
* * * * .FindFirst s
* * * * Debug.Print "NoMatch = " & .NoMatch

* * End With

* * Set r = Nothing

* * With .TableDefs("Employees")
* * * * .Indexes.Refresh
* * * * If i Is Nothing Then
* * * * * * Set i = .CreateIndex("Last Name")
* * * * * * With i
* * * * * * * * Set f = .CreateField("Last Name", dbText, 50)
* * * * * * * * .Fields.Append f
* * * * * * End With
* * * * * * .Indexes.Append i
* * * * Else
* * * * * * .Indexes.Delete "Last Name"
* * * * End If
* * * * .Indexes.Refresh
* * End With

* * .TableDefs.Refresh
* * Set i = Nothing

* * ' test presence of index "Last name"
* * On Error Resume Next
* * Set i = .TableDefs("Employees").Indexes("Last Name")
* * On Error GoTo 0
* * If i Is Nothing Then
* * * * d = "Not Indexed"
* * Else
* * * * d = "Indexed"
* * End If

* * Debug.Print String(10, "-") & vbNewLine & d

* * Set r = Nothing
* * .TableDefs.Refresh
* * Set r = .OpenRecordset("Employees", dbOpenDynaset)

* * With r
* * * * s = "[Last Name] = 'Ko""tas'"
* * * * Debug.Print s
* * * * .FindFirst s
* * * * Debug.Print "NoMatch = " & .NoMatch

* * * * .MoveFirst
* * * * s = "[Last Name] = ""Ko""""tas"""
* * * * Debug.Print s
* * * * .FindFirst s
* * * * Debug.Print "NoMatch = " & .NoMatch

* * End With

* * Debug.Print String(10, "-")

End With

End Sub
</code>

<immediate window>
Ko"tas
----------
Indexed
[Last Name] = 'Ko"tas'
NoMatch = False
[Last Name] = "Ko""tas"
NoMatch = True
----------
Not Indexed
[Last Name] = 'Ko"tas'
NoMatch = False
[Last Name] = "Ko""tas"
NoMatch = False
----------
</immediate window>

On May 25, 7:17*pm, "David W. Fenton" <XXXuse...@dfenton.com.invalid>
wrote:
nos...@nospam.nospam (Rick Collard) wrote innews:48399748.441597343@news..safepages.com:
Just to clarify, my [24" Diameter] was
fictitious designed only to demonstrate the problem. *I suppose a
more real world example would have sufficed:
rs.FindFirst "LastName = ""O'Reilly"""
rs.FindFirst "LastName = 'O''Rielly'"
This first line above works but the second fails to find the
record if it's a dynaset and LastName is indexed.
Well, of *course* it does, because you're not telling it the same
thing you're telling it with the first example.
I never use literal quotes for this. I always have a global constant
STR_QUOTE that holds Chr(34). I then do this:
* rs.FindFirst "LastName = " & STR_QUOTE & "O'Reilly" & STR_QUOTE
The result is that I *always* get correct results.
*Unfortunately, preventing the
use of any quotes in the field in the application where this
problem surfaced is not an option. *I appreciate your time
confirming the problem. *There are workarounds but I'll be
cautious with FindFirst in the future.
You're just not constructing your strings correctly. That is not the
fault of .FindFirst, but your error in telling it to look for the
wrong thing.
If you're really concerned about it, then you should parse your
criteria and replace all quotes with "?" and then use LIKE as your
comparison operator.
But the problem is *not* with .FindFirst. It's clear pilot error on
your part.
--
David W. Fenton * * * * * * * * *http://www.dfenton.com/
usenet at dfenton dot com * *http://www.dfenton.com/DFA/
Jun 27 '08 #17

P: n/a
In response to the tremendous interest shown it may be worthwhile to
note that we can defeat the "bug" by querying a pseudo calculated
field as in:
Set r = .OpenRecordset("SELECT TRIM([Last Name]) As Surname FROM
Employees", dbOpenDynaset)

Now when we FindFirst Surname Access/Jet/Ace does not use the index
and always finds [Ko"'pas].

Also helping to zero this in on FindFirst I note that the index and
seek work correctly:

That is:

Set r = .OpenRecordset("Employees", dbOpenTable)
With r
.Index = "Last Name"
.MoveFirst
.Seek "=", "Ko""tas"
MsgBox .NoMatch
End With

shows false.
On May 26, 9:10*am, lyle fairfield <lyle.fairfi...@gmail.comwrote:
Someone might notice the omission of a .MoveFirst in the second round
of testing. I've corrected that and it makes no difference to the
results.

On May 26, 8:37*am, lyle fairfield <lyle.fairfi...@gmail.comwrote:
I initially thought that the problem was in Rick's syntax. I was
wrong.
I wrote and ran this code (in Northwinds 2007 - I expect it would run
OK in previous versions) to test his findings independently.
Unless I am making an error in logic or syntax it shows that Access/
Jet/Ace DOES NOT find (with FindFirst)
"[Last Name] = ""Ko""""tas"""
when an index on [Last Name] exists
but DOES find
"[Last Name] = ""Ko""""tas"""
when an index on [Last Name] does not exist.
It finds
"[Last Name] = 'Ko""tas'"
regardless of the presence of the index.
After some further reflection I think this anomaly could be considered
a small bug; small only because it occurs in the unusual (to me,
anyhow) circumstance of having a double-quote in a text field.
<code>
Sub TestFindFirst()
Dim r As DAO.Recordset
Dim i As DAO.Index
Dim f As DAO.Field
Dim d$
Dim s$
With DBEngine(0)(0)
* * ' be sure we're current
* * .TableDefs.Refresh
* * .TableDefs("Employees").Indexes.Refresh
* * ' confirm value of field is Ko"tas
* * ' use syntax in question
* * .Execute ("UPDATE Employees SET [Last Name] = ""Ko""""tas"" WHERE
ID = 3")
* * Debug.Print .OpenRecordset("SELECT [Last Name] FROM Employees
WHERE ID = 3")(0)
* * Set r = .OpenRecordset("Employees", dbOpenDynaset)
* * ' test presence of index "Last name"
* * On Error Resume Next
* * Set i = .TableDefs("Employees").Indexes("Last Name")
* * 'On Error GoTo 0
* * If i Is Nothing Then
* * * * d = "Not Indexed"
* * Else
* * * * d = "Indexed"
* * End If
* * Debug.Print String(10, "-") & vbNewLine & d
* * With r
* * * * ' test single quotes
* * * * s = "[Last Name] = 'Ko""tas'"
* * * * Debug.Print s
* * * * .FindFirst s
* * * * Debug.Print "NoMatch = " & .NoMatch
* * * * ' tets double quotes
* * * * .MoveFirst
* * * * s = "[Last Name] = ""Ko""""tas"""
* * * * Debug.Print s
* * * * .FindFirst s
* * * * Debug.Print "NoMatch = " & .NoMatch
* * End With
* * Set r = Nothing
* * With .TableDefs("Employees")
* * * * .Indexes.Refresh
* * * * If i Is Nothing Then
* * * * * * Set i = .CreateIndex("Last Name")
* * * * * * With i
* * * * * * * * Set f = .CreateField("Last Name", dbText, 50)
* * * * * * * * .Fields.Append f
* * * * * * End With
* * * * * * .Indexes.Append i
* * * * Else
* * * * * * .Indexes.Delete "Last Name"
* * * * End If
* * * * .Indexes.Refresh
* * End With
* * .TableDefs.Refresh
* * Set i = Nothing
* * ' test presence of index "Last name"
* * On Error Resume Next
* * Set i = .TableDefs("Employees").Indexes("Last Name")
* * On Error GoTo 0
* * If i Is Nothing Then
* * * * d = "Not Indexed"
* * Else
* * * * d = "Indexed"
* * End If
* * Debug.Print String(10, "-") & vbNewLine & d
* * Set r = Nothing
* * .TableDefs.Refresh
* * Set r = .OpenRecordset("Employees", dbOpenDynaset)
* * With r
* * * * s = "[Last Name] = 'Ko""tas'"
* * * * Debug.Print s
* * * * .FindFirst s
* * * * Debug.Print "NoMatch = " & .NoMatch
* * * * .MoveFirst
* * * * s = "[Last Name] = ""Ko""""tas"""
* * * * Debug.Print s
* * * * .FindFirst s
* * * * Debug.Print "NoMatch = " & .NoMatch
* * End With
* * Debug.Print String(10, "-")
End With
End Sub
</code>
<immediate window>
Ko"tas
----------
Indexed
[Last Name] = 'Ko"tas'
NoMatch = False
[Last Name] = "Ko""tas"
NoMatch = True
----------
Not Indexed
[Last Name] = 'Ko"tas'
NoMatch = False
[Last Name] = "Ko""tas"
NoMatch = False
----------
</immediate window>
On May 25, 7:17*pm, "David W. Fenton" <XXXuse...@dfenton.com.invalid>
wrote:
nos...@nospam.nospam (Rick Collard) wrote innews:48****************@news.safepages.com:
Just to clarify, my [24" Diameter] was
fictitious designed only to demonstrate the problem. *I suppose a
more real world example would have sufficed:
rs.FindFirst "LastName = ""O'Reilly"""
rs.FindFirst "LastName = 'O''Rielly'"
This first line above works but the second fails to find the
record if it's a dynaset and LastName is indexed.
Well, of *course* it does, because you're not telling it the same
thing you're telling it with the first example.
I never use literal quotes for this. I always have a global constant
STR_QUOTE that holds Chr(34). I then do this:
* rs.FindFirst "LastName = " & STR_QUOTE & "O'Reilly" & STR_QUOTE
The result is that I *always* get correct results.
*Unfortunately, preventing the
use of any quotes in the field in the application where this
problem surfaced is not an option. *I appreciate your time
confirming the problem. *There are workarounds but I'll be
cautious with FindFirst in the future.
You're just not constructing your strings correctly. That is not the
fault of .FindFirst, but your error in telling it to look for the
wrong thing.
If you're really concerned about it, then you should parse your
criteria and replace all quotes with "?" and then use LIKE as your
comparison operator.
But the problem is *not* with .FindFirst. It's clear pilot error on
your part.
--
David W. Fenton * * * * * * * * *http://www.dfenton.com/
usenet at dfenton dot com * *http://www.dfenton.com/DFA/
Jun 27 '08 #18

P: n/a
Thanks again for confirming Lyle. I really needed a reality check on
this one. I ran your code in Access 2002 with the same results. To
confirm that the problem is limited to dynasets, I changed the two
occurrences of dbOpenDynaset to dbOpenSnapshot in your code and
verified that FindFirst works correctly with snapshots. The problem
is limited to indexed text fields with dynasets.

Yes, this is not an enormous bug. But I am surprised it apparently
exists in all the versions of DAO used with Access 2 through 2007.
I've searched all of the code in our main product for the use of
FindFirst and only a few instances use an indexed text field. And of
those few instances only one or two are with fields that may contain a
single or double quote. So I'll agree that it's an uncommon problem.
Nevertheless, it's alarming when FindFirst does not find what it
should.

On Mon, 26 May 2008 05:37:37 -0700 (PDT), lyle fairfield
<ly************@gmail.comwrote:
>I initially thought that the problem was in Rick's syntax. I was
wrong.

I wrote and ran this code (in Northwinds 2007 - I expect it would run
OK in previous versions) to test his findings independently.

Unless I am making an error in logic or syntax it shows that Access/
Jet/Ace DOES NOT find (with FindFirst)
"[Last Name] =3D ""Ko""""tas"""
when an index on [Last Name] exists
but DOES find
"[Last Name] =3D ""Ko""""tas"""
when an index on [Last Name] does not exist.

It finds
"[Last Name] =3D 'Ko""tas'"
regardless of the presence of the index.

After some further reflection I think this anomaly could be considered
a small bug; small only because it occurs in the unusual (to me,
anyhow) circumstance of having a double-quote in a text field.

<code>
Sub TestFindFirst()
Dim r As DAO.Recordset
Dim i As DAO.Index
Dim f As DAO.Field

Dim d$
Dim s$

With DBEngine(0)(0)

' be sure we're current
.TableDefs.Refresh
.TableDefs("Employees").Indexes.Refresh

' confirm value of field is Ko"tas
' use syntax in question
.Execute ("UPDATE Employees SET [Last Name] =3D ""Ko""""tas"" WHERE
ID =3D 3")
Debug.Print .OpenRecordset("SELECT [Last Name] FROM Employees
WHERE ID =3D 3")(0)

Set r =3D .OpenRecordset("Employees", dbOpenDynaset)

' test presence of index "Last name"
On Error Resume Next
Set i =3D .TableDefs("Employees").Indexes("Last Name")
'On Error GoTo 0
If i Is Nothing Then
d =3D "Not Indexed"
Else
d =3D "Indexed"
End If

Debug.Print String(10, "-") & vbNewLine & d

With r
' test single quotes
s =3D "[Last Name] =3D 'Ko""tas'"
Debug.Print s
.FindFirst s
Debug.Print "NoMatch =3D " & .NoMatch

' tets double quotes
.MoveFirst
s =3D "[Last Name] =3D ""Ko""""tas"""
Debug.Print s
.FindFirst s
Debug.Print "NoMatch =3D " & .NoMatch

End With

Set r =3D Nothing

With .TableDefs("Employees")
.Indexes.Refresh
If i Is Nothing Then
Set i =3D .CreateIndex("Last Name")
With i
Set f =3D .CreateField("Last Name", dbText, 50)
.Fields.Append f
End With
.Indexes.Append i
Else
.Indexes.Delete "Last Name"
End If
.Indexes.Refresh
End With

.TableDefs.Refresh
Set i =3D Nothing

' test presence of index "Last name"
On Error Resume Next
Set i =3D .TableDefs("Employees").Indexes("Last Name")
On Error GoTo 0
If i Is Nothing Then
d =3D "Not Indexed"
Else
d =3D "Indexed"
End If

Debug.Print String(10, "-") & vbNewLine & d

Set r =3D Nothing
.TableDefs.Refresh
Set r =3D .OpenRecordset("Employees", dbOpenDynaset)

With r
s =3D "[Last Name] =3D 'Ko""tas'"
Debug.Print s
.FindFirst s
Debug.Print "NoMatch =3D " & .NoMatch

.MoveFirst
s =3D "[Last Name] =3D ""Ko""""tas"""
Debug.Print s
.FindFirst s
Debug.Print "NoMatch =3D " & .NoMatch

End With

Debug.Print String(10, "-")

End With

End Sub
</code>

<immediate window>
Ko"tas
----------
Indexed
[Last Name] =3D 'Ko"tas'
NoMatch =3D False
[Last Name] =3D "Ko""tas"
NoMatch =3D True
----------
Not Indexed
[Last Name] =3D 'Ko"tas'
NoMatch =3D False
[Last Name] =3D "Ko""tas"
NoMatch =3D False
----------
</immediate window>

Rick Collard
www.msc-lims.com
Jun 27 '08 #19

P: n/a
no****@nospam.nospam (Rick Collard) wrote in
news:48****************@news.safepages.com:
On 25 May 2008 23:12:35 GMT, "David W. Fenton"
<XX*******@dfenton.com.invalidwrote:
>>no****@nospam.nospam (Rick Collard) wrote in
news:48****************@news.safepages.com:
>>I have concluded that FindFirst is unsafe when used with an
indexed text field.

On what grounds? The examples discussed in your post don't
constitute anything wrong with it, seems to me.

The example code in my original post demonstrates the problem. If
the delimiter used with FindFirst, whether a single quote or a
double quote, also occurs in the text string FindFirst will fail
if the field is indexed. Further testing shows the problem is
limited to dynasets. Jet's query processor handles the syntax
properly.
This is not a problem with .FindFirst. It's a problem in your code.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jun 27 '08 #20

P: n/a
no****@nospam.nospam (Rick Collard) wrote in
news:48****************@news.safepages.com:
On 25 May 2008 23:17:52 GMT, "David W. Fenton"
<XX*******@dfenton.com.invalidwrote:
>>no****@nospam.nospam (Rick Collard) wrote in
news:48****************@news.safepages.com:
>>Just to clarify, my [24" Diameter] was
fictitious designed only to demonstrate the problem. I suppose
a more real world example would have sufficed:

rs.FindFirst "LastName = ""O'Reilly"""

rs.FindFirst "LastName = 'O''Rielly'"

This first line above works but the second fails to find the
record if it's a dynaset and LastName is indexed.

Well, of *course* it does, because you're not telling it the same
thing you're telling it with the first example.

I believe I am telling it the same thing. It's difficult to
distinguish between two single quotes and one double quote. The
two examples just use different delimiters. Here are the
equivalent queries in SQL:

SELECT * FROM tblTest
WHERE LastName = "O'Reilly"

SELECT * FROM tblTest
WHERE LastName = 'O''Reilly'

The second example above uses all single quotes but there are two
back-to-back.
No, you're not telling it the same thing. Single quote and double
quote mean the same thing for enclosing a string, but if the quote
you use for enclosing the string occurs within the string you're
enclosing, then it causes problems, regardless of whether you use
single or double quotes. The meaning of the internal quote
characters changes according to which enclosing quote you use.
>>I never use literal quotes for this. I always have a global
constant STR_QUOTE that holds Chr(34). I then do this:

rs.FindFirst "LastName = " & STR_QUOTE & "O'Reilly" & STR_QUOTE

The result is that I *always* get correct results.
>> Unfortunately, preventing the
use of any quotes in the field in the application where this
problem surfaced is not an option. I appreciate your time
confirming the problem. There are workarounds but I'll be
cautious with FindFirst in the future.

You're just not constructing your strings correctly. That is not
the fault of .FindFirst, but your error in telling it to look for
the wrong thing.

Use the exact WHERE clauses from the SELECT statements above,
without the word WHERE, with FindFirst and one will succeed and
the other will fail. But it will only fail with a dynaset on an
indexed field. Drop the index and it works. Use a snapshot and
it will work.
And it's *your* error, not .FindFirst's.
>>If you're really concerned about it, then you should parse your
criteria and replace all quotes with "?" and then use LIKE as your
comparison operator.

But the problem is *not* with .FindFirst. It's clear pilot error
on your part.

Sorry, but this is not pilot error.
We'll have to disagree on that.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jun 27 '08 #21

P: n/a
no****@nospam.nospam (Rick Collard) wrote in
news:48****************@news.safepages.com:
it's alarming when FindFirst does not find what it
should.
It will find it if you tell it the right thing to look for, instead
of bullheadedly insisting that it should work with what *you* tell
it (even when you're telling it the wrong thing).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jun 27 '08 #22

P: n/a
David, let's try to cut this to the minimum.

r is a dao recordset of type dynaset. In it is a field (not calculated)
called "Last Name".
The underlying table has [Kot"as] as the data in the "Last Name" field of
one record.

We try to find that record as below:

s = "[Last Name] = ""Ko""""tas"""
r.FindFirst s

If this never worked we could say there is no problem with FindFirst, but,
rather a problem with the code. But it DOES work (it finds the record) in
all cases except when two conditions are met:

1. r must be of type dynaset;
2. There must be an index in the table on the field "Last Name".

Under those conditions FindFirst does NOT find the record.

It is that FindFirst acts differently when "Last Name" is indexed than when
it isn't indexed that prompts us to say there is a bug, not that it can't
find our syntax. Our syntax is irrelevant to this discussion. Whether or
not we should allow KoĒtas is irrelevant. The only thing really pertinent
is that FindFirst finds with no index existing and fails to find with an
index existing.

I think Iíve exhausted my input to this thread.

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in
news:Xn**********************************@64.209.0 .92:
This is not a problem with .FindFirst. It's a problem in your code.
Jun 27 '08 #23

P: n/a
On 27 May 2008 01:27:51 GMT, "David W. Fenton"
<XX*******@dfenton.com.invalidwrote:
>no****@nospam.nospam (Rick Collard) wrote in
news:48****************@news.safepages.com:
>it's alarming when FindFirst does not find what it
should.

It will find it if you tell it the right thing to look for, instead
of bullheadedly insisting that it should work with what *you* tell
it (even when you're telling it the wrong thing).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
If you know how to warn FindFirst that it's using a dynaset and the
criterion includes an indexed text field, please share.

Rick Collard
www.msc-lims.com
Jun 27 '08 #24

P: n/a
no****@nospam.nospam (Rick Collard) wrote in
news:48****************@news.safepages.com:
On 27 May 2008 01:27:51 GMT, "David W. Fenton"
<XX*******@dfenton.com.invalidwrote:
>>no****@nospam.nospam (Rick Collard) wrote in
news:48****************@news.safepages.com:
>>it's alarming when FindFirst does not find what it
should.

It will find it if you tell it the right thing to look for,
instead of bullheadedly insisting that it should work with what
*you* tell it (even when you're telling it the wrong thing).

If you know how to warn FindFirst that it's using a dynaset and
the criterion includes an indexed text field, please share.
It isn't a matter of distinguishing those, it's a matter of encoding
your criteria in a manner that won't cause an error. That means
replacing quotes with ? and using LIKE instead.

Of course, I've never used .FindFirst with anything but PKs, so
would never encounter this kind of problem. But I *have* encountered
it in other contexts, and have done what's necessary to avoid the
problem.

And, BTW, someone in the thread suggested that the criteria in the
form 'O'Leary' would work in a query, but it doesn't. Or, at least,
that's what I understood the claim to be. So, to me, .FindFirst's
behavior is completely consistent with the rest of Access/Jet.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jun 27 '08 #25

P: n/a

"lyle fairfield" <ly******@yah00.caschreef in bericht news:Xn************************@216.221.81.119...
David, let's try to cut this to the minimum.

r is a dao recordset of type dynaset. In it is a field (not calculated)
called "Last Name".
The underlying table has [Kot"as] as the data in the "Last Name" field of
one record.

We try to find that record as below:

s = "[Last Name] = ""Ko""""tas"""
r.FindFirst s

If this never worked we could say there is no problem with FindFirst, but,
rather a problem with the code. But it DOES work (it finds the record) in
all cases except when two conditions are met:

1. r must be of type dynaset;
2. There must be an index in the table on the field "Last Name".

Under those conditions FindFirst does NOT find the record.

It is that FindFirst acts differently when "Last Name" is indexed than when
it isn't indexed that prompts us to say there is a bug, not that it can't
find our syntax. Our syntax is irrelevant to this discussion. Whether or
not we should allow Ko"tas is irrelevant. The only thing really pertinent
is that FindFirst finds with no index existing and fails to find with an
index existing.
Hi Lyle,
FWIW I totally agree with you and Rick here.
Syntax is not relevant in *this* discussion.
I would call this a bug also. I don't like these 'surprises' ...

Arno R

Jun 27 '08 #26

This discussion thread is closed

Replies have been disabled for this discussion.