I am trying to traverse the querydefs collection, to list every field in
the query. so far so good. However, some of my queries have calculated
fields and I would like to access the definition of the calculation
field but do not know how or where it is stored.

If fo example I define a new field in a query


CalEndDate: IIF(not isnull([Enddate]), [Enddate], #12/31/2999#)

I would like to be able to find this definition in the querydef and
include it in my report.

How do I go about this?

Examples?

Yes, I have spent hours reading, searching to no joy.

thanks

Bob

On 03/02/2011 04:21:12, Bob Alston wrote:
Have you had a look at the MySysQueries Table (normally hidden System
Object)? Phil

On 03/02/2011 09:18:08, "Phil" wrote:
This may get you started

SELECT MSysObjects.Name, MSysObjects.Type, MSysQueries.*
FROM MSysQueries INNER JOIN MSysObjects ON MSysQueries.ObjectId =
MSysObjects.Id WHERE (((MSysObjects.Name) Not Like "~*") AND
((MSysObjects.Type)=5) AND ((MSysQueries.Expression) Is Not Null));

Queries are type 5, so you don't want forms Type -32768, Reports Type -32764,
Modules type -32761, Tables Type 1 or 6 and other types
SQL statements for combo noxes, list boxes etc. start with a ~, so you may
not want these. Gather you're not interested in queries with no expressions,
so I have eliminated them

Phil

On Feb 2, 11:21*pm, Bob Alston <bobalst... (AT) yahoo (DOT) com> wrote:
When parsing the SQL from the SQL property of the QueryDef, note that
the 'AS' keyword used to alias the field name can also be used to
alias table names. Before the 'AS' keyword you might go back to the
first comma or space unless square brackets (or the single quote
equivalents used by Lyle Fairfield) are encountered. Maybe see if the
parsed field name equates to a table to decide between field and table
aliases. Perhaps consider having your parsing cover the alternate
syntax used by Jamie Collins to reference tables in other databases.
What do you want your reporting to do if you have subqueries? It
seems there are possibly many proverbial flies in the ointment.

James A. Fortune
CDMAPoster (AT) FortuneJames (DOT) com

On 2/3/2011 2:35 PM, James A. Fortune wrote:
query, from accessing the querydef via DAO and NOT trying to parse the
SQL syntax. I have already gotten several pieces of data from the
querydef. This is just one piece I can't seem to get.

Bob

On Feb 3, 3:51*pm, Bob Alston <bobalst... (AT) yahoo (DOT) com> wrote:
Bob,

It might be possible to get an answer by accessing the querydef
collection itself, but a way to do that eludes me presently. Let us
know if you happen upon a solution. Do you also identify query
parameter fields?

James A. Fortune
CDMAPoster (AT) FortuneJames (DOT) com

On 2/3/2011 2:51 PM, Bob Alston wrote:
I was able to do so via Msysqueries and Msysobjects.

I Started with a table of queries and their fields, which I obtained
from DAO code and the queries object.

I then ran a query to link to Msys objects matching the queryname to
Name in Msysobjects. I saved the data from my original table and the
ID field from Msysobjects. I saved that query.

I then used the query saved above in a new query that matched the field
name from my original table to Name1 in Msysqueries and matched the ID
number from the first query to the ID number in Msysqueries.

Bob