Is it possible to list all of the tables/queries in a query via code?

Thank you - that worked.

However, I was hoping it would print the source table/query name in front of
the field name. So I'm back where I started.

Is there a way to list the tables/queries that comprise a single query?

"John Spencer" wrote in message
news:%%23Uh9ZsXyIHA.4912@TK2MSFTNGP03.phx.gbl...

Did you try the suggestion of using the SourceTable?

In John's code:
Debug.Print fld.Name & " = " & fld.SourceTable & "." & fld.SourceField

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"JimP" pockmiresolutions.com> wrote in message
news:O_adnWEBooUQuNDVnZ2dnUVZ_oninZ2d@comcast.com...

Thanks - will try it.

"Allen Browne" wrote in message
news:uZ%%231sCkyIHA.548@TK2MSFTNGP06.phx.gbl...

..not sure anyone is still following this thread. I tried SourceTable.
Unfortunately the table(s) I am looking for are used to filter records and
are not included in the query grid.

"JimP" pockmiresolutions.com> wrote in message
news:8-udnVxZlK8re9LVnZ2dnUVZ_o7inZ2d@comcast.com...

Jim, I dont' follow that.

If the table has no fields in the query, I don't see how you can get the
table name for the field that is not in the query from the query.

Guess I don't follow what you are doing, but you may have moved on to
something else now anyway.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"JimP" pockmiresolutions.com> wrote in message
news:t6ydncma3tWyJ8_VnZ2dnUVZ_vzinZ2d@comcast.com...

You're right.

The table in this case is a temp table of "alpha codes" created from a
multi-select list box on a form. The routine that creates the tables,
converts all codes to lower case to eliminate the possibility of duplicates.
The temp table in turn links to SQL Server tables to extract data. Some of
the SQL Server tables have a mixture of upper/lower case codes.

I need to find which queries have the temp table to edit the SELECT
statement to force lower case joins (to many queries to manually inspect) -
case sensitivity is enabled.

I didn't design the SQL Server db. Ms-Access is just being used as a report
writer and I'm trying to assure that the table joins in queries are correct.

Jim

"Allen Browne" wrote in message
news:e0$8kqdzIHA.5716@TK2MSFTNGP04.phx.gbl...

The following will provide a listing of all queries and their sources. If
the
source is another query, you'll need to scroll to that query to see the
table(s) as source.

One problem I've been unable to resolve: If the query is a SELECT Top
the number provided will show up in the Source column. I've attempted
to resolve this using the Val() function, i.e. if the Source is a number,
that will appear as a number, if not it'll show as 0. The intent is to
filter on the field (Expr1), select only those records with Expr1 = 0,
however this returns 'Data Type Mismatch in Criteria Expression'.

Have done several searches where posters have described this problem
but none of them have resulted in a successful solution, other than a
response that 'the Val function should work'.

Thanks to RuralGuy at
http://www.access-programmers.co.uk/forums/showthread.php?p=715086&posted=1#post715086
the problem is resolved. Try this: