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

Another JET flaw

P: n/a
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.
Nov 13 '05 #1
Share this Question
Share on Google+
16 Replies


P: n/a
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.

Nov 13 '05 #2

P: n/a
"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.
Nov 13 '05 #3

P: n/a
"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.
Nov 13 '05 #4

P: n/a
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

Nov 13 '05 #5

P: n/a
"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
Nov 13 '05 #6

P: n/a
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
Nov 13 '05 #7

P: n/a
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

Nov 13 '05 #8

P: n/a
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

Nov 13 '05 #9

P: n/a
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.

Nov 13 '05 #10

P: n/a
"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.
Nov 13 '05 #11

P: n/a
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.

Nov 13 '05 #12

P: n/a
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.
Nov 13 '05 #13

P: n/a
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.

Nov 13 '05 #14

P: n/a
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.

Nov 13 '05 #15

P: n/a
"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.
Nov 13 '05 #16

P: n/a
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().
Nov 13 '05 #17

This discussion thread is closed

Replies have been disabled for this discussion.