473,320 Members | 1,870 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Bug warning: DAO 3.6 FindFirst with Like operator

Microsoft acknowledges a bug with this, but the bug is worse than they say.

It turns out that if you use the Like operator without a wildcard on a
snapshot type recordset, all sorts of wierd, undesirable things can happen.
This situation can arise when you need something like a list of match
conditions, some of which are exact matches.

According to Microsoft, the bug is that it will treat all expressions without
wildcards as if they are followed by *. In fact, I found out that any single
character expression such as "B" will match everything! Basically, what this
comes down to is that you should never use FindFirst with Like on a Snapshot
recordset unless you are certain the expression -will- contain at least one
wildcard character.

Nov 12 '05 #1
10 5119
Steve Jorgensen <no****@nospam.nospam> wrote in
news:d1********************************@4ax.com:
Microsoft acknowledges a bug with this, but the bug is worse than they
say.

It turns out that if you use the Like operator without a wildcard on a
snapshot type recordset, all sorts of wierd, undesirable things can
happen. This situation can arise when you need something like a list of
match conditions, some of which are exact matches.

According to Microsoft, the bug is that it will treat all expressions
without wildcards as if they are followed by *. In fact, I found out
that any single character expression such as "B" will match everything!
Basically, what this comes down to is that you should never use
FindFirst with Like on a Snapshot recordset unless you are certain the
expression -will- contain at least one wildcard character.


What do you want it to do when you ask it to:
..FindFirst "fldDescription LIKE 'B'"

I wonder how may of us would shrug our shoulders and say, "MS cannot
predict every peculiarity of coding!", and how many of us would say,
"Bug!" in reponse to this.

I would say the first of the two.

I note that ADO
(
..CursorType = adOpenStatic
..LockType = adLockReadOnly
)
does not find any records for:
..Find "fldDescription LIKE 'B'"
and moves the record pointer to .EOF
(quite properly IMO)
unless there is a record with the value
"B" (only)
in the searched field
in which case it
"finds" that record.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Nov 12 '05 #2
no****@nospam.nospam (Steve Jorgensen) wrote in
<d1********************************@4ax.com>:
Microsoft acknowledges a bug with this, but the bug is worse than
they say.

It turns out that if you use the Like operator without a wildcard
on a snapshot type recordset, all sorts of wierd, undesirable
things can happen. This situation can arise when you need
something like a list of match conditions, some of which are exact
matches.
I don't quite understand the use of LIKE without an asterisk.
Wouldn't it be equivalent to "=" ?

I use BuildCriteria for constructing all my WHERE clauses, so I
never code to cover both exactl and LIKE, which is the only
scenario I can think of where you'd use LIKE without the asterisk.
According to Microsoft, the bug is that it will treat all
expressions without wildcards as if they are followed by *. In
fact, I found out that any single character expression such as "B"
will match everything! Basically, what this comes down to is that
you should never use FindFirst with Like on a Snapshot recordset
unless you are certain the expression -will- contain at least one
wildcard character.


Well, D'oh!

Why would anyone make a practice of using LIKE except with a
wildcard, except coding laziness?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #3
On Mon, 19 Jan 2004 18:09:30 GMT, dX********@bway.net.invalid (David W.
Fenton) wrote:
no****@nospam.nospam (Steve Jorgensen) wrote in
<d1********************************@4ax.com>:
Microsoft acknowledges a bug with this, but the bug is worse than
they say.

It turns out that if you use the Like operator without a wildcard
on a snapshot type recordset, all sorts of wierd, undesirable
things can happen. This situation can arise when you need
something like a list of match conditions, some of which are exact
matches.
I don't quite understand the use of LIKE without an asterisk.
Wouldn't it be equivalent to "=" ?


Yes, exactly. In my case, I have a list of rules for how to import from
columns in an import table that can vary from month to month. For some rules,
I know the exact name, and for some rules, I know an invariant part of the
name. I have a table of rules where the match condition is a Like expression,
and if the match is an exact field name, there are no wildcard characters.
This should be a valid thing to want to do, right?
I use BuildCriteria for constructing all my WHERE clauses, so I
never code to cover both exactl and LIKE, which is the only
scenario I can think of where you'd use LIKE without the asterisk.
According to Microsoft, the bug is that it will treat all
expressions without wildcards as if they are followed by *. In
fact, I found out that any single character expression such as "B"
will match everything! Basically, what this comes down to is that
you should never use FindFirst with Like on a Snapshot recordset
unless you are certain the expression -will- contain at least one
wildcard character.


Well, D'oh!

Why would anyone make a practice of using LIKE except with a
wildcard, except coding laziness?


See above. Like should act like "=" for expressions with no wildcards. Why
should I not expect to take advantage of this fact when I have a list of
criteria, some exact, and some not? As far as I'm concerned, coding laziness
of this sort can be a -good- thing. Use the functionality that -should- be
built in instead of having to write your own code that duplicates that. Now,
I admit, I've recently been advocating doing otherwise when it improves code
clarity and designs out common programmer errors, but this is not one of those
cases.
Nov 12 '05 #4
On 19 Jan 2004 12:40:42 GMT, Lyle Fairfield <Mi************@Invalid.Com>
wrote:
Steve Jorgensen <no****@nospam.nospam> wrote in
news:d1********************************@4ax.com :
Microsoft acknowledges a bug with this, but the bug is worse than they
say.

It turns out that if you use the Like operator without a wildcard on a
snapshot type recordset, all sorts of wierd, undesirable things can
happen. This situation can arise when you need something like a list of
match conditions, some of which are exact matches.

According to Microsoft, the bug is that it will treat all expressions
without wildcards as if they are followed by *. In fact, I found out
that any single character expression such as "B" will match everything!
Basically, what this comes down to is that you should never use
FindFirst with Like on a Snapshot recordset unless you are certain the
expression -will- contain at least one wildcard character.
What do you want it to do when you ask it to:
.FindFirst "fldDescription LIKE 'B'"

I wonder how may of us would shrug our shoulders and say, "MS cannot
predict every peculiarity of coding!", and how many of us would say,
"Bug!" in reponse to this.

I would say the first of the two.


Really? I would say that commercial code should include unit tests that would
prevent this sort of thing. In fact, this is one of the first tests I would
write since it's a boundary condition. Also, I don't think it's that
peculiar. I was trying to take advantage of the fact that a Like comparison
with no wildcards is the same as an "=" because I have a list of conditions,
some exact, and some partial. Why should I not expect let Like take care of
the distinction for me?

I do agree that this is not the most aggregious error you could make, and the
work-arounds are not hard. I just thought a warning was in order.

In any case, I just switched to a Dynaset for the easy work-around.

I note that ADO
(
.CursorType = adOpenStatic
.LockType = adLockReadOnly
)
does not find any records for:
.Find "fldDescription LIKE 'B'"
and moves the record pointer to .EOF
(quite properly IMO)
unless there is a record with the value
"B" (only)
in the searched field
in which case it
"finds" that record.


I'm aware that ADO does not have the problem, which is expected. In the case
of an MDB front-end to an MDB, though, I still prefer to stick with DAO.

Nov 12 '05 #5
"Steve Jorgensen" <no****@nospam.nospam> wrote...
This should be a valid thing to want to do, right?
Generally, no. Since such a query would not be optimized properly anyway.
Better to make the code a big more specific o be faster (and the fact that
it will avoid bugs is the bonus feature of the approach!).
As far as I'm concerned, coding laziness of this sort can be a -good-

thing.

Without testing and finding out you have to work a bit harder here? I do not
think so. :-)
--
MichKa [MS]
NLS Collation/Locale/Keyboard Development
Globalization Infrastructure and Font Technologies

This posting is provided "AS IS" with
no warranties, and confers no rights.

Nov 12 '05 #6
On Mon, 19 Jan 2004 15:16:29 -0800, "Michael \(michka\) Kaplan [MS]"
<mi*****@online.microsoft.com> wrote:
"Steve Jorgensen" <no****@nospam.nospam> wrote...
This should be a valid thing to want to do, right?


Generally, no. Since such a query would not be optimized properly anyway.
Better to make the code a big more specific o be faster (and the fact that
it will avoid bugs is the bonus feature of the approach!).
As far as I'm concerned, coding laziness of this sort can be a -good-

thing.

Without testing and finding out you have to work a bit harder here? I do not
think so. :-)


Clearly, you turn out to be right, but I would not have thought that for
"Like" to work properly for the case of no wildcards was an unsafe assumption.
Also, since "Like" optimizes to a bounded range search when it begins with a
constant (OK, I know that's for querying, not filtering), that's also optimal
for the case of no wildcard since it will be a bounded range with upper and
lower bounds equal.

Also, since I was trying to filter a medium-small recordset in memory, I
wasn't too concerned about performance optimization.
Nov 12 '05 #7
no****@nospam.nospam (Steve Jorgensen) wrote in
<63********************************@4ax.com>:
I'm aware that ADO does not have the problem, which is expected.
In the case of an MDB front-end to an MDB, though, I still prefer
to stick with DAO.


I wouldn't count it continuing to work in ADO, either.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #8
TC

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:43********************************@4ax.com...

(snip)
I would not have thought that for "Like" to work properly
for the case of no wildcards was an unsafe assumption.

For me, it's a matter of not making >any< assumptions beyond what is
explicitly stated in the language documentation.

Unfortunately I don't have Access here to check. But if the documentation
for Like says that it is used with wildcards - and the syntax shows it being
used with wildcards - and the examples show it being used wth wildcards -
and there is no explicit statement that you can use it >without< wildcards -
then, there really is no justification for making the assumption in
question.

Of course, you could test it, to see if it worked the way you assumed. But
even if it did, how safe would it be, to rely upon that? If a behaviour is
not explicitly stated in the language documentation, it could easily change
in the next version, because of internal changes (for example).

To take another (better) example, it's like assuming that an "IF ... AND ...
AND ..." statement will be evaluated left-to-right, or in "short circuit"
fashion, in a language where the documentation does not explicitly state
those things. Even if you did some testing to find out how a particular
implementation worked, there is no guarantee that any future version and/or
other implementation, would work the same way.

So, my rule is - "No Assumptions!"

TC
Nov 12 '05 #9
On Tue, 20 Jan 2004 13:03:10 +1030, "TC" <a@b.c.d> wrote:

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:43********************************@4ax.com.. .

(snip)
I would not have thought that for "Like" to work properly
for the case of no wildcards was an unsafe assumption.

For me, it's a matter of not making >any< assumptions beyond what is
explicitly stated in the language documentation.


But what I was couting on was the documented behavior. Microsoft acknowledges
that the current behavior is a bug.
Unfortunately I don't have Access here to check. But if the documentation
for Like says that it is used with wildcards - and the syntax shows it being
used with wildcards - and the examples show it being used wth wildcards -
and there is no explicit statement that you can use it >without< wildcards -
then, there really is no justification for making the assumption in
question.
It says that a any non-wildcard characters will be matched exactly.
So, my rule is - "No Assumptions!"


I agree that one should not make assumptions about behavior that's not
documented. In this case, though, the documentation is clear enough,
microsoft simply didn't test their code well enough. The definition of Like
doesn't say that it will work as documented so long as at least one of the
characters is a wildcard, otherwise all bets are off.
Nov 12 '05 #10
TC
Ok, I shouldn't have commented without reading the Help. I'll do that
tonight, & comment back if necessary :-)

TC
(off for the day)
"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:90********************************@4ax.com...
On Tue, 20 Jan 2004 13:03:10 +1030, "TC" <a@b.c.d> wrote:

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:43********************************@4ax.com.. .

(snip)
I would not have thought that for "Like" to work properly
for the case of no wildcards was an unsafe assumption.

For me, it's a matter of not making >any< assumptions beyond what is
explicitly stated in the language documentation.


But what I was couting on was the documented behavior. Microsoft

acknowledges that the current behavior is a bug.
Unfortunately I don't have Access here to check. But if the documentation
for Like says that it is used with wildcards - and the syntax shows it beingused with wildcards - and the examples show it being used wth wildcards -
and there is no explicit statement that you can use it >without< wildcards -then, there really is no justification for making the assumption in
question.
It says that a any non-wildcard characters will be matched exactly.
So, my rule is - "No Assumptions!"


I agree that one should not make assumptions about behavior that's not
documented. In this case, though, the documentation is clear enough,
microsoft simply didn't test their code well enough. The definition of

Like doesn't say that it will work as documented so long as at least one of the
characters is a wildcard, otherwise all bets are off.

Nov 12 '05 #11

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: mikron30 | last post by:
In the following code I am getting the following warning warning C4213: nonstandard extension used : cast on l-value class X { public: operator bool() {}; }; class Y
2
by: Mark | last post by:
I get "error 3077 Syntax Error (missing operator) in expression" in this expression: Rst.FindFirst "='" & Me!NewSection & "'" when Me!NewSection contains an apostrophe. How can I write the...
5
by: Paul | last post by:
The 2 statements below work perfectly when using them individually. But when I try to concatenate them, they don't. rst.FindFirst " = " & OldQuoteNumber rst.FindFirst " Is Null" Can someone...
1
by: Mike MacSween | last post by:
rstStuAddr.MoveFirst rstStuAddr.FindFirst "CountryID = 168 AND Left(CLPostCode,4) <> 'BFPO'" CountryID and CLPostCode are names of fields in rstStuAddr It works. But doesn't look like it...
6
by: Shyguy | last post by:
Is there a way to use this when entries have an apostrphe in them? Like Joe's Bar and Grill.
7
by: waltvw | last post by:
I'm using FindFirst method in Access VBA to find a particular record in a recordset. I have 2 search criteria each of which works just fine if used separately as an argument in FindFirst, but NOT in...
25
by: Rick Collard | last post by:
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...
3
by: Thomas Lenz | last post by:
The code below should allow to use a comma instead of << with ostreams and include a space between two operands when comma is used. e.g. cout << "hello", "world", endl; should print the line...
13
by: ChrisD76 | last post by:
Hi, I am new to using VBA, and have been working with DAO Recordsets. I'm working on a little problem, and think that DAO Recordsets are the solution. I've been playing around with them to try and...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.