Order of predicates in where clause important - invalid use of null

[Yitzak]

Hi spent a few hours on this one wrote a query that joined on results
of 2 other queries.

Qry3 using Qry1 and Qry2
When I used Qry1.FasText <cstr(Qry2.FasInteger) in the where clause
- got invalid use of null.

Fair enough Field1 and Field2 can contain nulls

I altered the 2 queries to exclude nulls from FasText and FasInteger-
same error.
Excluded nulls in Qry3 - same error.

The solution was to move "FasInteger is not null" to be the first part
of the where clause. This should NOT matter...

further rant..
This reminds of my first experience of Access where left outer join
was doing the same as an inner join - later found out was using old
version of jet. Still it should have produced an error not done the
next best thing...

Would you fly in a plane that used Access as its DB - I certainly
wouldn't

[Tim Marshall]

Yitzak wrote:

Otherwise its a bug...

*Or* you don't understand what you're working with. I don't really care
about Jet, because as I mentioned all of my professional work is against
Oracle with Jet being used only for tinkering. Jet certainly has its
limitations but like Oracle or any other dbms, mixing views can be
perilous if you don't know for sure what you're doing. 8) But by the
sounds of your post, it's appears inconceiveable that anything might be
wrong with yourwork on anything! 8)
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

[Matthias Klaey]

Yitzak <te*********@yahoo.co.ukwrote:

Hi spent a few hours on this one wrote a query that joined on results
of 2 other queries.

Qry3 using Qry1 and Qry2
When I used Qry1.FasText <cstr(Qry2.FasInteger) in the where clause
- got invalid use of null.

Fair enough Field1 and Field2 can contain nulls

I altered the 2 queries to exclude nulls from FasText and FasInteger-
same error.
Excluded nulls in Qry3 - same error.

The solution was to move "FasInteger is not null" to be the first part
of the where clause. This should NOT matter...

This sounds very interesting. Please could you provide the complete
and explicit SQL statements for your example, including table
definitions and example values so that we can reproduce your results?

Greetings
Matthias Kläy
--
www.kcc.ch

[Yitzak]

On 6 Jul, 02:52, Matthias Klaey <[e-mail]:

Yitzak <[e-mail]:

Hi spent a few hours on this one wrote a query that joined on results
of 2 other queries.

Qry3 using Qry1 and Qry2
When I used Qry1.FasText <cstr(Qry2.FasInteger) in the where clause
- got invalid use of null.

Fair enough Field1 and Field2 can contain nulls

I altered the 2 queries to exclude nulls from FasText and FasInteger-
same error.
Excluded nulls in Qry3 - same error.

The solution was to move "FasInteger is not null" to be the first part
of the where clause. This should NOT matter...

This sounds very interesting. Please could you provide the complete
and explicit SQL statements for your example, including table
definitions and example values so that we can reproduce your results?

Greetings
Matthias Kläy
--www.kcc.ch

Matthias - its not feasible.

The problem arose from joining 2 queries, both queries were based on
Huge tables not just in terms of number of rows but also number of
columns (DB schema is not relational :-).

The 2 queries which were joined to form new query were joined using
left outer join The field that was used in the where clause which had
to be moved so as new query did not error was *NOT* the field used in
the join.

All base tables are linked via ODBC to a SQL Server DB. Is this the
issue? that is ODBC

The query has other "weirdness" e.g. it compiled/saved even though it
says things like
numberfield Like "1" numberfield is a numeric field. I thought this
would error with type mismatch??

[lyle]

On Jul 7, 9:07 pm, Yitzak <[e-mail]:

On 6 Jul, 02:52, Matthias Klaey <[e-mail]:

Yitzak <[e-mail]:

Hi spent a few hours on this one wrote a query that joined on results
of 2 other queries.

Qry3 using Qry1 and Qry2
When I used Qry1.FasText <cstr(Qry2.FasInteger) in the where clause
- got invalid use of null.

Fair enough Field1 and Field2 can contain nulls

I altered the 2 queries to exclude nulls from FasText and FasInteger-
same error.
Excluded nulls in Qry3 - same error.

The solution was to move "FasInteger is not null" to be the first part
of the where clause. This should NOT matter...

This sounds very interesting. Please could you provide the complete
and explicit SQL statements for your example, including table
definitions and example values so that we can reproduce your results?

Greetings
Matthias Kläy
--www.kcc.ch

Matthias - its not feasible.

The problem arose from joining 2 queries, both queries were based on
Huge tables not just in terms of number of rows but also number of
columns (DB schema is not relational :-).

The 2 queries which were joined to form new query were joined using
left outer join The field that was used in the where clause which had
to be moved so as new query did not error was *NOT* the field used in
the join.

All base tables are linked via ODBC to a SQL Server DB. Is this the
issue? that is ODBC

The query has other "weirdness" e.g. it compiled/saved even though it
says things like
numberfield Like "1" numberfield is a numeric field. I thought this
would error with type mismatch??

I used to have a strange query. When I ran it in the morning three
pink flamingoes engaged in group sex on my front lawn. When I ran it
in the evening, George Bush seemed to make sense. I'm sorry I can't
provide you with more details as the whole thing was very weird. But
you can take my word for it: Access is fatally flawed.

[Arch]

On Sun, 08 Jul 2007 03:49:22 -0000, lyle <ly************@gmail.com>
wrote:

>
I used to have a strange query. When I ran it in the morning three
pink flamingoes engaged in group sex on my front lawn. When I ran it
in the evening, George Bush seemed to make sense. I'm sorry I can't
provide you with more details as the whole thing was very weird. But
you can take my word for it: Access is fatally flawed.

Lyle, I've enjoyed your wit and effective use of satire for some time
now. The last time that I voiced approval in this newsgroup, however,
I was promptly killfiled by Stephen LeBans. Since I don't wish to
incur the displeasure of Mr LeBans or anyone else for that matter,
I'll refrain from further comment.

[Yitzak]

On 8 Jul, 18:11, Matthias Klaey <[e-mail]:

Now the question remains: *Why* do you get this "unexpected" behavior,
and can it be classified as a "bug" of Access? I will leave the answer
to other people, but I would first look at the query execution plan.

HTH
Matthias Kläy
--www.kcc.ch

Forgot - Access can show you the query execution plan. How? This would
be invaluable to me - so that I can tell if certain queries are using
indexes..

[Matthias Klaey]

Yitzak <te*********@yahoo.co.ukwrote:

On 8 Jul, 18:11, Matthias Klaey <[e-mail]:

Now the question remains: *Why* do you get this "unexpected" behavior,
and can it be classified as a "bug" of Access? I will leave the answer
to other people, but I would first look at the query execution plan.

HTH
Matthias Kläy
--www.kcc.ch

Forgot - Access can show you the query execution plan. How? This would
be invaluable to me - so that I can tell if certain queries are using
indexes..

See, e.g.,

http://articles.techrepublic.com.com...2-5064388.html

Greetings
Matthias Kläy
--
www.kcc.ch

[Matthias Klaey]

Yitzak <te*********@yahoo.co.ukwrote:

THANKS for the Nz - I was looking for the equivalent of isnull(fld,'')
function.

I cannot find the help, just need reference really of ACCESS's dialect
of SQL i.e. a list of all the reserved words in Access's version of
SQL. does such a thing exist in the help files? E.G. a search for Nz
in my help file produces nothing.

To find the help topics you are interested in, open any code module,
then click "Help / Microsoft Visual Basic Help" on the menu. This
should get you to the Visual Basic Help/Table of Contents.

You will find Nz under the "Microsoft Access Visual Basic
Reference/Functions" which is an extension of "Microsoft Visual Basic
Reference" for the Access object model.

Using functions like Nz or CStr in query expressions is not exactly
part of SQL, it is available since Jet uses the Visual Basic
Expression Service, thus this is a (very powerful) extension of SQL.

SQL you will find at the same place in "Microsoft Jet SQL Reference".
Greetings
Matthias Kläy
--
www.kcc.ch

[Yitzak]

On 8 Jul, 20:59, Matthias Klaey <[e-mail]:

Yitzak <[e-mail]:

On 8 Jul, 18:11, Matthias Klaey <[e-mail]:

Now the question remains: *Why* do you get this "unexpected" behavior,
and can it be classified as a "bug" of Access? I will leave the answer
to other people, but I would first look at the query execution plan.

HTH
Matthias Kläy
--www.kcc.ch

Forgot - Access can show you the query execution plan. How? This would
be invaluable to me - so that I can tell if certain queries are using
indexes..

See, e.g.,

http://articles.techrepublic.com.com...2-5064388.html

Greetings
Matthias Kläy
--www.kcc.ch

Fantastic - I've only ever used query plans to see if it a query is
using an index or if table scanning to decide what index to put on;
Exactly what I was looking for.

VBA help - obvious when I think about it (maybe should try a bit more
of it) - I'm a newbie to Access.

Checked Nz out - it is a function of Access.Application class - know
where to look now..

Thanks again Matthias , and also Tina who answered my previous post -
I've learnt alot about Access from your replies.