FindFirst fails on indexed field

[Rick Collard]

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.Worksp aces(0).Databas es(0)
Set rs = db.OpenRecordse t("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

https://www.msc-lims.com

[Rick Collard]

Re: FindFirst fails on indexed field

On Wed, 21 May 2008 01:50:27 GMT, nospam@nospam.n ospam (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.Worksp aces(0).Databas es(0)
Set rs = db.OpenRecordse t("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

https://www.msc-lims.com

[Salad]

Re: FindFirst fails on indexed field

Rick Collard wrote:

On Wed, 21 May 2008 01:50:27 GMT, nospam@nospam.n ospam (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.Worksp aces(0).Databas es(0)
> Set rs = db.OpenRecordse t("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

[Rick Collard]

Re: FindFirst fails on indexed field

On Fri, 23 May 2008 10:26:32 -0700, Salad <oil@vinegar.co mwrote:

>
>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

https://www.msc-lims.com

[Salad]

Re: FindFirst fails on indexed field

Salad wrote:

Rick Collard wrote:
>

>On Wed, 21 May 2008 01:50:27 GMT, nospam@nospam.n ospam (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.Worksp aces(0).Databas es(0)
>> Set rs = db.OpenRecordse t("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...usin g the Replace function
to convert " to in.

[Salad]

Re: FindFirst fails on indexed field

Rick Collard wrote:

On Fri, 23 May 2008 10:26:32 -0700, Salad <oil@vinegar.co mwrote:
>
>

>>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?

[Rick Collard]

Re: FindFirst fails on indexed field

On Fri, 23 May 2008 15:01:51 -0700, Salad <oil@vinegar.co mwrote:

>Rick Collard wrote:

>On Fri, 23 May 2008 10:26:32 -0700, Salad <oil@vinegar.co mwrote:
>>
>>

>>>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

https://www.msc-lims.com

[Salad]

Re: FindFirst fails on indexed field

Rick Collard wrote:

On Fri, 23 May 2008 15:01:51 -0700, Salad <oil@vinegar.co mwrote:
>
>

>>Rick Collard wrote:
>>

>>>On Fri, 23 May 2008 10:26:32 -0700, Salad <oil@vinegar.co mwrote:
>>>
>>>
>>>
>>>>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

[Rick Collard]

Re: FindFirst fails on indexed field

On Fri, 23 May 2008 19:11:27 -0700, Salad <oil@vinegar.co mwrote:

>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

https://www.msc-lims.com

[lyle fairfield]

Re: FindFirst fails on indexed field

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.n ospam (Rick Collard) wrote:

I have concluded that FindFirst is unsafe when used with an indexed
text field.  Thanks for the discussion.

[Rick Collard]

Re: FindFirst fails on indexed field

On Sun, 25 May 2008 08:51:59 -0700 (PDT), lyle fairfield
<lyle.fairfield @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.n ospam (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

https://www.msc-lims.com

[David W. Fenton]

Re: FindFirst fails on indexed field

nospam@nospam.n ospam (Rick Collard) wrote in
news:48398232.4 36199541@news.s afepages.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/

[David W. Fenton]

Re: FindFirst fails on indexed field

nospam@nospam.n ospam (Rick Collard) wrote in
news:48399748.4 41597343@news.s afepages.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/

[Rick Collard]

Re: FindFirst fails on indexed field

On 25 May 2008 23:17:52 GMT, "David W. Fenton"
<XXXusenet@dfen ton.com.invalid wrote:

>nospam@nospam. nospam (Rick Collard) wrote in
>news: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 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

https://www.msc-lims.com

[Rick Collard]

Re: FindFirst fails on indexed field

On 25 May 2008 23:12:35 GMT, "David W. Fenton"
<XXXusenet@dfen ton.com.invalid wrote:

>nospam@nospam. nospam (Rick Collard) wrote in
>news:48398232. 436199541@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

https://www.msc-lims.com