Wake up, 97 Query in 2000/03

[Martin]

I've converted a 97 database to 2000 (seems to do the same in 2003) and one
of the select queries gives me no records when there should be quite a few.
I tried copying the SQL to a new query and, lo and behold, it works fine. It
feels like the SQL isn't compiling properly. Is there a better solution as
I've got loads of these queries, hardwired into a number of reports and it's
going to take a while to go through them all.

The only thing to say about this particular query is that it was written
some time ago and the designer was obviously happier with writing by hand
than using the QBE grid because s/he used single letter aliases for the table
names throughout. Could this somehow be the problem?

 

[Chris Mills]

The only difference between A97 queries and giving no results on later Access,
that I know of, is that it's sometimes now necessary to put
WHERE Trim(<something>) = SOMETHING
which would previously just have been
WHERE <something> = SOMETHING

I don't know if this is usual or not, but I got into the habit of doing it
anyway from A2000 onwards. Since stored text fields are trimmed anyway, I'm
not about to theorise.

Of course, this would not explain your problem, ie you got it working anyway.
I believe A2000 is extremely flakey as to conversions, that is if conversion
completes and doesn't just "lock-up". Regardless, I prefer to do a full
decompile/compile/compact after conversion, and before even any testing. I do
this often because I maintain code in A97 with regular auto-conversion.

"auto-conversion" - get it? - haha!

I believe that SQL is partially or somehow "pre-compiled" or "optimised". I do
not know whether the undocumented "/decompile" switch affects this (usually
it's to clean-up code). I'd do it as a matter of course anyway.

Chris

 

[Allen Browne]

Most queries convert and work just fine. However, JET 4 (the query engine in
A2000 and later) is not as good at guessing data types as JET 3.5 (A97),
IME.

Examples where it has to guess the data type include:
- values picked up from unbound controls on a form;
- undeclared parameters in a query;
- calculated fields.

If that is what you are experiencing, you could solve the problem by:
- setting the Format property of the text box/combo, e.g. General Number, or
Short Date;
- declaring your parameters: Parameters on Query menu in query design;
- typecasting your calculated fields with CLng(), CVDate(), etc.

More info:
Calculated fields misinterpreted
at:
http://members.iinet.net.au/~allenbrowne/ser-45.html

 

[Albert D. Kallal]

The only difference between A97 queries and giving no results on later
Access,
that I know of, is that it's sometimes now necessary to put
WHERE Trim(<something>) = SOMETHING
which would previously just have been
WHERE <something> = SOMETHING

I don't know if this is usual or not, but I got into the habit of doing it
anyway from A2000 onwards. Since stored text fields are trimmed anyway,
I'm
not about to theorise.

Hum, I can't say I every needed to do the trim. And, you should know that
wrapping a field name like:

where trim([CompanyName] = 'My Cool company'

means that indexing can NOT be used! This will slow down your application by
VERY large amount. I would find out why that original query failed. (could
have been just one extra space or something!). Wrapping the field name in a
trim is not the best idea here.

Perhaps you imported some data from another source (like Excel...or perahps
some text file) that did not have the data in fields trimmed in the first
place. However, the solution to that is to run a update query with a trim
command to remove that extra stuff ONCE. This will eliminate the need for
the trim each time, and more important allow ms-access to using indexing.
So, you don't want to use any functions around fields unless you have to....

 

[Chris Mills]

You make good points, Albert. Though I'm not worried about it now, it was NOT
just a few records (it applied to the whole table) and it was NOT imported.

It is true I never pursued the cause with the vigilance of an Old West Posse.
It is also true that I have never really seen anyone else report it over many
years. Allen Browne's post was interesting to me as a POSSIBLE cause, since
he's a lawman with a known fine aim.

I can only say I have seen a difference in converting queries from A97 to A2k
(why is that?), and reported it. I agree that trimming everything could
destroy indexing, so I wish to make a tactical (tactfull) withdrawal beyond
saying it's worthwhile trying, just for a test mind.

Clearly, Trim means I was referring to a Text field, and if it was a floating
or some other types of field searches then indexes may not apply anyway. If
I'm the only one to report it, then I happily accept that.

Chris

 

[david epsom dot com dot au]

- undeclared parameters in a query;

We had trouble even with declared parameters! (I think
we declared most of our parameters in the conversion from
Jet 2.5 to Jet 3.5). In particular, we had problems in
A2K where we were assigning a numeric value to a date field,
and where we were assigning a parameter value to a memo
field.

(david)

 

[david epsom dot com dot au]

First compact a couple of times. That is /supposed/ to cure problems
like this. Then try a code loop to reset each query:

dim qdf as object, qdfs as object
set qdf = codedb.querydefs
for each qdf in qdfs
qdf.sql = qdf.sql
next qdf

if the sql is hardwired into reports or controls,
loop through the controls collection of the report documents.

than using the QBE grid because s/he used single letter aliases
names throughout. Could this somehow be the problem?

Well if these are typed into the report properties, sometimes
using table aliases just makes it easier to read. If they
have been re-written by hand, it does make it more likely that
the brackets and order of the items might have changed. Perhaps
that is it?

The only thing I can think of that might have caused this
problem is use of report properties inside the sql (like
[report] or [name] or [myTextBox]. Because properties don't
always get created correctly, dependant sql might have problems
as well.

(david)

 

[Jamie Collins]

Perhaps you imported some data from another source (like Excel...or perahps
some text file) that did not have the data in fields trimmed in the first
place.

I'm always keen to get info on how using MS Access differs from MS
Jet.

Using ADO and OLEDB provider for Jet 4, if I do this:

CREATE TABLE MyTable (
MyTextCol VARCHAR(255) NOT NULL)
;
INSERT INTO MyTable (MyTextCol)
VALUES (' Hello world! ')
;
SELECT LEN(MyTextCol) AS char_count
FROM MyTable
;

I get the expected result of 16 i.e. no trimming. I then open the .mdb
in Access2003 (on a virtual machine) drop the table and repeat, I get
the same results i.e. no trimming.

What is supposed to be doing the trimming?

Jamie.

--

 

[Martin]

Thanks everyone. There don't seem to be any of the issues mentioned by Allen
in this query - just two tables, a number of fields and nothing more
"calculated" than an IIF function. I tried David's idea of
decompiling/compiling and also his query-definition loop to reset each
query's SQL (as well as a DAO version of my own) to no avail: opening this
query just gives an empty dynaset.

I still think it is to do with the compiling of the SQL because if I make a
change and resave (I've been deleting the final semicolon) it works fine -
slowly the first time but quickly thereafter. I'll just have to do the same
with the other queries in case.

Just one of those crazy things...

First compact a couple of times. That is /supposed/ to cure problems
like this. Then try a code loop to reset each query:

dim qdf as object, qdfs as object
set qdf = codedb.querydefs
for each qdf in qdfs
qdf.sql = qdf.sql
next qdf

if the sql is hardwired into reports or controls,
loop through the controls collection of the report documents.

than using the QBE grid because s/he used single letter aliases
names throughout. Could this somehow be the problem?

Well if these are typed into the report properties, sometimes
using table aliases just makes it easier to read. If they
have been re-written by hand, it does make it more likely that
the brackets and order of the items might have changed. Perhaps
that is it?

The only thing I can think of that might have caused this
problem is use of report properties inside the sql (like
[report] or [name] or [myTextBox]. Because properties don't
always get created correctly, dependant sql might have problems
as well.

(david)

I've converted a 97 database to 2000 (seems to do the same in 2003) and one
of the select queries gives me no records when there should be quite a few.
I tried copying the SQL to a new query and, lo and behold, it works fine. It
feels like the SQL isn't compiling properly. Is there a better solution as
I've got loads of these queries, hardwired into a number of reports and it's
going to take a while to go through them all.

The only thing to say about this particular query is that it was written
some time ago and the designer was obviously happier with writing by hand
than using the QBE grid because s/he used single letter aliases for the table
names throughout. Could this somehow be the problem?

 

[Allen Browne]

IIf() is a calculated field.
You already mentioned that typecasting made a difference.

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

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

Thanks everyone. There don't seem to be any of the issues mentioned by
Allen
in this query - just two tables, a number of fields and nothing more
"calculated" than an IIF function. I tried David's idea of
decompiling/compiling and also his query-definition loop to reset each
query's SQL (as well as a DAO version of my own) to no avail: opening this
query just gives an empty dynaset.

I still think it is to do with the compiling of the SQL because if I make
a
change and resave (I've been deleting the final semicolon) it works fine -
slowly the first time but quickly thereafter. I'll just have to do the
same
with the other queries in case.

Just one of those crazy things...

First compact a couple of times. That is /supposed/ to cure problems
like this. Then try a code loop to reset each query:

dim qdf as object, qdfs as object
set qdf = codedb.querydefs
for each qdf in qdfs
qdf.sql = qdf.sql
next qdf

if the sql is hardwired into reports or controls,
loop through the controls collection of the report documents.

than using the QBE grid because s/he used single letter aliases
names throughout. Could this somehow be the problem?

Well if these are typed into the report properties, sometimes
using table aliases just makes it easier to read. If they
have been re-written by hand, it does make it more likely that
the brackets and order of the items might have changed. Perhaps
that is it?

The only thing I can think of that might have caused this
problem is use of report properties inside the sql (like
[report] or [name] or [myTextBox]. Because properties don't
always get created correctly, dependant sql might have problems
as well.

(david)

I've converted a 97 database to 2000 (seems to do the same in 2003) and one
of the select queries gives me no records when there should be quite a few.
I tried copying the SQL to a new query and, lo and behold, it works
fine. It
feels like the SQL isn't compiling properly. Is there a better
solution as
I've got loads of these queries, hardwired into a number of reports and it's
going to take a while to go through them all.

The only thing to say about this particular query is that it was
written
some time ago and the designer was obviously happier with writing by
hand
than using the QBE grid because s/he used single letter aliases for the table
names throughout. Could this somehow be the problem?

 

[Albert D. Kallal]

What is supposed to be doing the trimming?

If you build a form...and edit the data...it is trimmed (trailing blanks).

So, no actually trimming occurs when you use code, or sql to edit the
data...but if you edit data using a ms-access form, then trailing blanks are
removed.

So, that is kind of why I mentioned the issue of importing data from some
other source.

So, it really is a UI issue here...and not a coding issue. If you use
ms-access with JET, or Oracle...you get the same action..and that is
trailing blanks are trimmed for you.

 

[david epsom dot com dot au]

If you use Access or Jet with SQL Server, you ALSO get
SQL Server behaviour - which is that spaces are trimmed
in text fields (VarChar or nVarChar).

So you can have code (ADO/DAO) that writes spaces
to a text field in an MDB, but won't write the
same data to a SQL Server nVarChar field.

(david)

 

[Jamie Collins]

So, it really is a UI issue here...and not a coding issue. If you use
ms-access with JET, or Oracle...you get the same action..and that is
trailing blanks are trimmed for you.

Albert, Many thanks for this.

Jamie.

--