16 1547
Mr. Browne,
Maybe so. But for the tons of admin assistants out there using Access that
are not schooled in boolean algebra or versed in set theory or give a rats
but about relational theory the statement (4 <> Null) = Null defies logic.
4 does not equal Null they would say, and agree with Access that this is a
true statement. Access is an rdbms pitched as a database for the rest of
us. Many of Jet's flaws are changes to the orthodox way of doing things to
make life easier for the administrative assistants that often get roped into
building database applications without formal training.
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au... Access yields the wrong result for this simple query: SELECT (4 <> Null) AS Expr1;
Details: http://allenbrowne.com/bug-11.html
-- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org.
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au... Access yields the wrong result for this simple query: SELECT (4 <> Null) AS Expr1;
FWIW in ANSI SQL you cannot do this at all. Boolean data types cannot be
specified as the data type of a table column or variable, and cannot be
returned in a result set.
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in news:4173a408$0$25588
$5*******@per-qv1-newsreader-01.iinet.net.au: Access yields the wrong result for this simple query: SELECT (4 <> Null) AS Expr1;
Details: http://allenbrowne.com/bug-11.html
Uh huh!
--
Lyle
--
use iso date format: yyyy-mm-dd http://www.w3.org/QA/Tips/iso-date
--
The e-mail address isn't, but you could use it to find one.
A97 needs a source table, but still has the error:
SELECT (Employees.EmployeeID <> Null) AS Expr1,
(Null <> Employees.EmployeeID) AS Expr2
FROM Employees;
Output of Expr1 is True, and Expr2 is Null. Same error.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Chuck Grimsby" <c.*******@worldnet.att.net.invalid> wrote in message
news:gb********************************@4ax.com... This won't even run in Access 97. (Reserved Word error)
Access 2K and 2K3 both return -1 however, which is correct. 4 is not Null.
On Mon, 18 Oct 2004 19:07:46 +0800, "Allen Browne" <Al*********@SeeSig.Invalid> wrote:Access yields the wrong result for this simple query: SELECT (4 <> Null) AS Expr1; Details: http://allenbrowne.com/bug-11.html
"Alan Webb" <kn*****@hotmail.com> wrote in
news:D6********************@comcast.com: Maybe so. But for the tons of admin assistants out there using Access that are not schooled in boolean algebra or versed in set theory or give a rats but about relational theory the statement (4 <> Null) = Null defies logic. 4 does not equal Null they would say, and agree with Access that this is a true statement. Access is an rdbms pitched as a database for the rest of us. Many of Jet's flaws are changes to the orthodox way of doing things to make life easier for the administrative assistants that often get roped into building database applications without formal training.
That's the same philosophy behind Internet Explorer's rendering of
completely invalid HTML, which has led us to an Internet littered
with web pages that render legibly *only* in IE.
If it's logically wrong, it should not return a result that is a
guess as to what the user intends.
A SQL parser should not guess at what the user means.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
On Oct 18 2004, 07:07 am, "Allen Browne" <Al*********@SeeSig.Invalid> wrote
in news:41***********************@per-qv1-newsreader-01.iinet.net.au: Access yields the wrong result for this simple query: SELECT (4 <> Null) AS Expr1;
Details: http://allenbrowne.com/bug-11.html
Just to split hairs, this looks like a query parser bug as opposed to an
engine bug. The query below, where the NULL is passed through the
Expression Service, produces correct result:
SELECT (4<>IIf(True,Null)) AS Expr1;
--
remove a 9 to reply by email
Yes, it does seem to be a parsing bug, i.e. "<> Null" is parsed as "Is Not
Null", and so your expression is interpreted correctly.
(Not sure that the ES is involved though.)
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Dimitri Furman" <df*****@cloud99.net> wrote in message
news:Xn****************************@127.0.0.1... On Oct 18 2004, 07:07 am, "Allen Browne" <Al*********@SeeSig.Invalid> wrote in news:41***********************@per-qv1-newsreader-01.iinet.net.au:
Access yields the wrong result for this simple query: SELECT (4 <> Null) AS Expr1;
Details: http://allenbrowne.com/bug-11.html
Just to split hairs, this looks like a query parser bug as opposed to an engine bug. The query below, where the NULL is passed through the Expression Service, produces correct result:
SELECT (4<>IIf(True,Null)) AS Expr1;
-- remove a 9 to reply by email
David & the rest,
I am not defending the "bug", just pointing out that Microsoft tries very
hard to listen to its customers and does things to respond to what it
believes best serves the customer, standards be danged. Jet SQL steps on
the ASNI-SQL standards pretty hard in places--especially where doing so
makes life easier for the target audience of a product.
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.86... "Alan Webb" <kn*****@hotmail.com> wrote in news:D6********************@comcast.com:
Maybe so. But for the tons of admin assistants out there using Access that are not schooled in boolean algebra or versed in set theory or give a rats but about relational theory the statement (4 <> Null) = Null defies logic. 4 does not equal Null they would say, and agree with Access that this is a true statement. Access is an rdbms pitched as a database for the rest of us. Many of Jet's flaws are changes to the orthodox way of doing things to make life easier for the administrative assistants that often get roped into building database applications without formal training.
That's the same philosophy behind Internet Explorer's rendering of completely invalid HTML, which has led us to an Internet littered with web pages that render legibly *only* in IE.
If it's logically wrong, it should not return a result that is a guess as to what the user intends.
A SQL parser should not guess at what the user means.
-- David W. Fenton http://www.bway.net/~dfenton dfenton at bway dot net http://www.bway.net/~dfassoc
Alan Webb wrote: I am not defending the "bug", just pointing out that Microsoft tries very hard to listen to its customers and does things to respond to what it believes best serves the customer, standards be danged.
Point taken. But I have to say, if that is in fact Microsoft's
viewpoint, then it is 180 degrees off course in what it believes to be
what best serves the customer. Standards are standards. And that is
what's best for the customer.
Sloppiness is not good even if a majority think that way. And while I
know that you know what you're talking about technically, I heartily
disagree with what is best "philosophically" and real-world-applicable
in this case.
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<41***********************@per-qv1-newsreader-01.iinet.net.au>... Access yields the wrong result for this simple query: SELECT (4 <> Null) AS Expr1;
Details: http://allenbrowne.com/bug-11.html
Yup. But just to nitpick, you want to use the word "reflexive" rather
than "transitive". Transitive is:
if (A op B) and (B op C) then (A op C) is true
where "op" is replaced with a suitable something.
Fair enough. Altered.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Damien" <Da*******************@hotmail.com> wrote in message
news:ac**************************@posting.google.c om... Access yields the wrong result for this simple query: SELECT (4 <> Null) AS Expr1;
Details: http://allenbrowne.com/bug-11.html
Yup. But just to nitpick, you want to use the word "reflexive" rather than "transitive". Transitive is:
if (A op B) and (B op C) then (A op C) is true
where "op" is replaced with a suitable something.
Allen Browne wrote: Access yields the wrong result for this simple query: SELECT (4 <> Null) AS Expr1;
Details: http://allenbrowne.com/bug-11.html
Hi Allen. In A97, I can't enter
Expr1:(4 <> Null)
using the query builder.
As soon as I tab to the next field, the code changes to
Expr1:(4 Is Not Null)
So I tried
Sub Test()
Dim s As String
Dim r As Recordset
Dim i As Integer
s = "SELECT (4 <> Null) AS Expr1 From Employees"
Set r = CurrentDb.OpenRecordset(s, dbOpenSnapshot)
r.MoveFirst
Do While i < 4
i = i + 1
MsgBox r!Expr1
r.MoveNext
Loop
r.Close
Set r = Nothing
End Sub
and I get True for the 4 records. Since I can't get the SQL for the
recordset once opened, I'll assume it changed the (4<>Null) to (4 Is Not
Null). I'm sure if I were to assign it to a recordsource, I would see
that it has corrected my code.
I think Access is doing it's best to correct the developer in this case
whether or not you want the correction.
In the query design window, switch to SQL View (View menu).
You can type the statement in there, and it will retain the "<> Null".
I have no argument with the GUI trying to help the user figure out what they
meant. But, when the query parser/engine misinterprets the statement and
gives you a different answer than what you asked for, it is just plain
wrong.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Salad" <oi*@vinegar.com> wrote in message
news:wl*****************@newsread3.news.pas.earthl ink.net... Allen Browne wrote:
Access yields the wrong result for this simple query: SELECT (4 <> Null) AS Expr1;
Details: http://allenbrowne.com/bug-11.html
Hi Allen. In A97, I can't enter Expr1:(4 <> Null) using the query builder.
As soon as I tab to the next field, the code changes to Expr1:(4 Is Not Null)
So I tried Sub Test() Dim s As String Dim r As Recordset Dim i As Integer
s = "SELECT (4 <> Null) AS Expr1 From Employees" Set r = CurrentDb.OpenRecordset(s, dbOpenSnapshot)
r.MoveFirst Do While i < 4 i = i + 1 MsgBox r!Expr1 r.MoveNext Loop r.Close Set r = Nothing End Sub
and I get True for the 4 records. Since I can't get the SQL for the recordset once opened, I'll assume it changed the (4<>Null) to (4 Is Not Null). I'm sure if I were to assign it to a recordsource, I would see that it has corrected my code.
I think Access is doing it's best to correct the developer in this case whether or not you want the correction.
Allen Browne wrote: In the query design window, switch to SQL View (View menu). You can type the statement in there, and it will retain the "<> Null".
Hmmmm. I'm in A97. It autoconverts it to (4 Is Not Null). In my case
I'd never be able to enter your statement.
I have no argument with the GUI trying to help the user figure out what they meant. But, when the query parser/engine misinterprets the statement and gives you a different answer than what you asked for, it is just plain wrong.
I agree with you. Can't NZ() be used to overcome the problem.
"Salad" <oi*@vinegar.com> wrote in message
news:Hk****************@newsread1.news.pas.earthli nk.net... In the query design window, switch to SQL View (View menu). You can type the statement in there, and it will retain the "<> Null".
Hmmmm. I'm in A97. It autoconverts it to (4 Is Not Null). In my case I'd never be able to enter your statement.
Even in SQL View?
I see that behavior in Design View, but can enter the statement in SQL View.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
Allen Browne wrote: "Salad" <oi*@vinegar.com> wrote in message news:Hk****************@newsread1.news.pas.earthli nk.net...
In the query design window, switch to SQL View (View menu). You can type the statement in there, and it will retain the "<> Null". Hmmmm. I'm in A97. It autoconverts it to (4 Is Not Null). In my case I'd never be able to enter your statement.
Even in SQL View?
My mistake. As long as I never go into Design view it will keep the
syntax as (4 <> Null). IOW, I need to save it while in SQL view. Then
it opens with the command intact. The second I go enter into Design
view it "corrects" me.
I see that behavior in Design View, but can enter the statement in SQL View.
I mentioned earlier I tried this by opening a recordset. The values
returned True. So I created Table1; ID, NumberField, BoolField and
created a form off of this table. I then entered the following.
Dim s As String
s = "SELECT Table1.ID, Table1.NumberField, " & _
"(4 <> Null) AS BoolField FROM Table1 ORDER BY Table1.ID;"
Me.RecordSource = s
MsgBox Me.RecordSource
And it kept the select string as shown above. But the results came back
true.
I guess behind the scenes it "corrects" me.
You've found an interesting "feature". I guess I'd go with NZ(). This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Christian Engström |
last post by:
If you have a function that returns something by value, the gcc compiler
(version 3.2.3 on Windows XP with MinGW) converts the returned value
from the type you specify in the code, to the const...
|
by: ma740988 |
last post by:
I'm hoping my post here doesn't fall into the 'hard to say' category,
nonetheless I've been advised that multiple uses of accessor/mutator
(get/set) member functions can be viewed as a 'design...
|
by: christophe (dot) poucet (at) gmail (dot) com |
last post by:
Hello,
I noticed there is a flaw in the vector implementation of g++ (3.4).
Basically when you erase an element from a vector, it calls the wrong
destructor. In most cases this is not such a...
|
by: Sky |
last post by:
What I have currently:
I have a user control called mod_container.aspx that is basically two
divs -- the top a toolbar, that expands/collapse the second div which can
contain other...
|
by: Java script Dude |
last post by:
Give this a test in IE (verified in IE 6.0 sp2)
Has anybody else seen this and is there a workaround besides escaping
with html special chars and not using IE ;]
~ file 1 ~
<html>
<head>...
|
by: Matt Kruse |
last post by:
http://news.zdnet.com/2100-1009_22-6121608.html
Hackers claim zero-day flaw in Firefox
09 / 30 / 06 | By Joris Evers
SAN DIEGO--The open-source Firefox Web browser is critically flawed in...
|
by: mps |
last post by:
I want to define a class that has a generic parameter that is itself a
generic class. For example, if I have a generic IQueue<Tinterface, and
class A wants to make use of a generic class that...
|
by: Phlip |
last post by:
Javascripters:
I have an outer page and an inner iframe. The outer page calculates some
javascript, and wants the inner frame to run it. The inner frame should hit
a page on the same (private)...
|
by: Windows |
last post by:
Microsoft Windows | MSN Flaw
I have found a flaw in MSN. For those that use MSN or Live.com with
Windows see:...
|
by: Guy Macon |
last post by:
Serious Security Flaw in Google Chrome:
http://www.readwriteweb.com/archives/security_flaw_in_google_chrome.php
--
Guy Macon
<http://www.GuyMacon.com/>
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
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: 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: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
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: 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...
| |