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. 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) 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
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.
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.
"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.
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. 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
"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
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.
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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
|
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...
|
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...
|
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...
|
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.
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |