C
Craig E Shea
I have been working on a database for some time that includes many queries. I
have turned on the feature in Access 2003 so that it understands SQL92
queries. This implies, of course, that the query engine will now accept the
SQL92 wildcards (&, _, and the like) in lieu of the traditional Access
(pre-XP or 2000) wildcards (*, #, etc).
I have noticed however, that sometimes, the Reports RecordSource property
does not show all the data returned by a query when the query uses SQL92
wildcards. Note that I said sometimes. I have about 5 SELECT queries that
returns the same columns for 5 different vendors; the only thing that is
different is the WHERE clause (e.g. one query would have 'WHERE
VendorID="HE01"' while another may have 'WHERE VendorID="OM01"'...the former
works while the latter does not). I have found that if I change the queries
to use the previous Access wildcards compatible with the JET engine that the
report behaves as expected. Please note that this happens most often with
complex queries and UNION queries, however, in the example given above, the
SELECT queries were simple in nature, only returning a 6 fields. (And I can't
tell you how deeply nested (e.g. a query calling a query calling a query,
etc.) these queries might be, which could be the root of the problem.
The main reason I used SQL92 queries is so I can "upsize" the database
later, if needed and I anticipated that once the option was turned on to
allow SQL92 syntax queries that it would apply thoroughout the entire
application. I believe this to be a bug in your Reports area of Access2003.
Has anyone else run across this? Is it that I may have queries nested too
deeply? Anyone's reply on the matter is appreciated. Thanks in advance!!
have turned on the feature in Access 2003 so that it understands SQL92
queries. This implies, of course, that the query engine will now accept the
SQL92 wildcards (&, _, and the like) in lieu of the traditional Access
(pre-XP or 2000) wildcards (*, #, etc).
I have noticed however, that sometimes, the Reports RecordSource property
does not show all the data returned by a query when the query uses SQL92
wildcards. Note that I said sometimes. I have about 5 SELECT queries that
returns the same columns for 5 different vendors; the only thing that is
different is the WHERE clause (e.g. one query would have 'WHERE
VendorID="HE01"' while another may have 'WHERE VendorID="OM01"'...the former
works while the latter does not). I have found that if I change the queries
to use the previous Access wildcards compatible with the JET engine that the
report behaves as expected. Please note that this happens most often with
complex queries and UNION queries, however, in the example given above, the
SELECT queries were simple in nature, only returning a 6 fields. (And I can't
tell you how deeply nested (e.g. a query calling a query calling a query,
etc.) these queries might be, which could be the root of the problem.
The main reason I used SQL92 queries is so I can "upsize" the database
later, if needed and I anticipated that once the option was turned on to
allow SQL92 syntax queries that it would apply thoroughout the entire
application. I believe this to be a bug in your Reports area of Access2003.
Has anyone else run across this? Is it that I may have queries nested too
deeply? Anyone's reply on the matter is appreciated. Thanks in advance!!