MSAccess major Bug or am I loosing my marbles ?

  • Thread starter Thread starter ipk*
  • Start date Start date
I

ipk*

This MUST be a bug. There is no way this can be correct behaviour, or can
someone correct me ?

I am calculating a value test and want to evaluate whether it is greater or
lesser than 0.

The query shows me

0,099967 for my variable "test"

and returns

"-1" for my comparison if test>0.0.

I have googled bot not found anything related. I'd be happy to provide
any other data you like, even the whole database, if anybody could once more
nudge me into the right direction. This time, yes, I have even turned the
whole laptop off various times and had the database sitting for a week with
no work done on it, and the error would not go away ... (as it did with my
last questions posted here :-). My last resort was just to set the decimal
sign to "." as opposed to the german standard ",". Did not help either ...

By the way ... would this be related ??? I had a comparison query where two
numbers in scientific (exponential) format were compared, and found the
values are compared as strings (or so i figured), i.e. 7.8E-7 was shown to be
greater than 3.5E-5. This was an error that simply went away over night.
Still strange. Am I doing something fundamentally wrong here ?

The full query is appended below, in case someone would have an idea.

SELECT query_wellvolumes_via_table.GScreenId,
query_wellvolumes_via_table.SubstanceName,
query_wellvolumes_via_table.phGradientOnTheFly,
query_wellvolumes_via_table.volGScreenComponent AS RealVol,
DLookUp("[RobotMinimumVolume]","tbl_robots","[RobotName]='" &
[Formulare]![form_gscreen_pipetting_do]![txt_RobotName] & "'") AS MinVol,
[RealVol]-[MinVol] AS test,test>0.0 AS Pipetteable
FROM query_wellvolumes_via_table
WHERE (((query_wellvolumes_via_table.GScreenId)=1) AND
((query_wellvolumes_via_table.phGradientOnTheFly)=False));
 
There's a very good chance that this has to do with Access not recognising
the data type as you expect, and therefore performing the wrong kind of
comparison on the data.

Details in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html
 
This MUST be a bug. There is no way this can be correct behaviour, or
can someone correct me ?

I am calculating a value test and want to evaluate whether it is greater
or lesser than 0.

The query shows me

0,099967 for my variable "test"

and returns

"-1" for my comparison if test>0.0.
Click to expand...

What am I missing here? That number IS greater than zero and your test
for that is returning true (you know that -1 is the same as true right?).

Also, in many cases you cannot create an field name alias "test" and then
refer to the field by that alias in the same SELECT statement. Repeat
the expression instead.

(RealVol - MinVol) > 0.0 AS Pipetteable
 
The query shows me

0,099967 for my variable "test"

and returns

"-1" for my comparison if test>0.0.
Click to expand...

That is the correct result (-1 is True, 0 is False, and 0.099967 is greater
than zero).

What were you expecting?
 
Thanks to all of you for replying ...

Rick Brandt said:
What am I missing here? That number IS greater than zero and your test
for that is returning true (you know that -1 is the same as true right?).
Click to expand...

Heck ... I'll be ... how intuitive is that ... ? It is not like that I have
never programmed anything ever before ...

But, no, I did not know this, to my embarrasment ...

Thank you, thank you, thank you ...

Ingo

 
Rick Brandt said:
Also, in many cases you cannot create an field name alias "test" and then
refer to the field by that alias in the same SELECT statement. Repeat
the expression instead.

(RealVol - MinVol) > 0.0 AS Pipetteable
Click to expand...

EXCELLENT, because this was the next problem ...

I had broken the query down into smaller chunks and variables to better
trace trough what was going on, but then I could not filter for Pipetteable =
True. It would all of a sudden ask me vor [RealVol] in a popup (which it
seemed to know just fine if I would not filter).

Putting it back together all into one field everything now works as it should.

Oh boy ...

Are these actually general SQL issues, or is this JET specific ???

Thanks, once more ...

Ingo
 
ipk* said:
Rick Brandt said:
Also, in many cases you cannot create an field name alias "test" and
then refer to the field by that alias in the same SELECT statement.
Repeat
the expression instead.

(RealVol - MinVol) > 0.0 AS Pipetteable
Click to expand...

EXCELLENT, because this was the next problem ...

I had broken the query down into smaller chunks and variables to
better
trace trough what was going on, but then I could not filter for
Pipetteable = True. It would all of a sudden ask me vor [RealVol] in
a popup (which it seemed to know just fine if I would not filter).

Putting it back together all into one field everything now works as
it should.

Oh boy ...

Are these actually general SQL issues, or is this JET specific ???
Click to expand...

These? Which ones? The fact that -1 is interpreted as True? In the database
world, yes, that is Jet-specific. In SQL Server, 1 is interpreted as True.
However, in both Jet and SQL Server, 0 is interpreted as False, so many
people choose to test for false rather than true. Incidently, Jet follows
the Visual Basic model here, in that -1 is interpreted as True in
VB/VBA/vbscript. Actuall, any value besides 0 is interpreted as True in VB -
look at the result of CBool(3).

The variants of SQL I use behave similarly with regard to the using the
results of calculations in the same statement. When I need to use
calculation results in other calculations, I will sometimes create
subqueries to avoid having to retype the expressions:

select calcresult, <expression using calcresult> from
(select <expression> as calcresult from table)
 
David said:
Actually, it's a Visual Basic issue, no?
Click to expand...

I was never sure. If you use an update query to set a boolean column to -1,
the column will display True/Yes/On.
Whether that's due to VB or not, I never bothered to try and figure out.
 
I was never sure. If you use an update query to set a boolean
column to -1, the column will display True/Yes/On.
Whether that's due to VB or not, I never bothered to try and
figure out.
Click to expand...

What I meant was that the decision to store Boolean True as -1 in
Jet was likely due to the fact that Jet was developed by Microsoft
under the shadow of Visual Basic, and VB already used -1, so Jet was
designed to do so also.

But maybe I have the timeline wrong.

Interesting question, though. Are there any other
languages/databases that use -1 for True?
 
David said:
What I meant was that the decision to store Boolean True as -1 in
Jet was likely due to the fact that Jet was developed by Microsoft
under the shadow of Visual Basic, and VB already used -1, so Jet was
designed to do so also.
Click to expand...
It certainly made it easier to interface Jet and VBA, not having to
worry about different enumerations for true and false. But it wouldn't
have been impossible: ADO manages to properly interpret bit values
coming from SQL Server via the OLE DB Providers.
But maybe I have the timeline wrong.

Interesting question, though. Are there any other
languages/databases that use -1 for True?
Click to expand...

Hmm ... none that I'm aware of.
 
Probably related to binary masks and to the fact that 16 bits set to 1 = FFh
= -1 (as signed integer, 255 as unsigned integers, but VBA does not handle
unsigned integers natively).


Vanderghast, Access MVP
 
Interesting question, though. Are there any other
languages/databases that use -1 for True?
Click to expand...

DEC PDP/10 TOPS ca. 1970 certainly did, so it's at least got some history.
 
Yap. That was it. As you correctly said, I had to wrap the values I was
comparing in CDbl() to allow access to figure out that 0.333 is greater than
9.9E-7.

Oh boy ...

that is almost as good as going back to punch cards ... what fun !!!

Ingo

Allen Browne said:
There's a very good chance that this has to do with Access not recognising
the data type as you expect, and therefore performing the wrong kind of
comparison on the data.

Details in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


ipk* said:
This MUST be a bug. There is no way this can be correct behaviour, or can
someone correct me ?

I am calculating a value test and want to evaluate whether it is greater
or
lesser than 0.

The query shows me

0,099967 for my variable "test"

and returns

"-1" for my comparison if test>0.0.

I have googled bot not found anything related. I'd be happy to provide
any other data you like, even the whole database, if anybody could once
more
nudge me into the right direction. This time, yes, I have even turned the
whole laptop off various times and had the database sitting for a week
with
no work done on it, and the error would not go away ... (as it did with my
last questions posted here :-). My last resort was just to set the decimal
sign to "." as opposed to the german standard ",". Did not help either ...

By the way ... would this be related ??? I had a comparison query where
two
numbers in scientific (exponential) format were compared, and found the
values are compared as strings (or so i figured), i.e. 7.8E-7 was shown to
be
greater than 3.5E-5. This was an error that simply went away over night.
Still strange. Am I doing something fundamentally wrong here ?

The full query is appended below, in case someone would have an idea.

SELECT query_wellvolumes_via_table.GScreenId,
query_wellvolumes_via_table.SubstanceName,
query_wellvolumes_via_table.phGradientOnTheFly,
query_wellvolumes_via_table.volGScreenComponent AS RealVol,
DLookUp("[RobotMinimumVolume]","tbl_robots","[RobotName]='" &
[Formulare]![form_gscreen_pipetting_do]![txt_RobotName] & "'") AS MinVol,
[RealVol]-[MinVol] AS test,test>0.0 AS Pipetteable
FROM query_wellvolumes_via_table
WHERE (((query_wellvolumes_via_table.GScreenId)=1) AND
((query_wellvolumes_via_table.phGradientOnTheFly)=False));
Click to expand...
Click to expand...
 
It certainly made it easier to interface Jet and VBA, not having
to worry about different enumerations for true and false. But it
wouldn't have been impossible: ADO manages to properly interpret
bit values coming from SQL Server via the OLE DB Providers.
Click to expand...

Yes, but that's by definition a data translation layer. Access uses
Jet natively, as the data store for MDB files, so it needs to "speak
Jet" without a translation layer.
Hmm ... none that I'm aware of.
Click to expand...

Wonder why they chose it?
 
David said:
Yes, but that's by definition a data translation layer. Access uses
Jet natively, as the data store for MDB files, so it needs to "speak
Jet" without a translation layer.


Wonder why they chose it?
Click to expand...
vanderghast's guess is as good as any, but I suspect we'll never know
for sure, will we.
 
Back
Top