Table with prefix 'Lookup_'-prefix in filter-query

[me83]

First I apply a 'filter by form' on a form with one or more combo-box(es) and secondly I save the filter with the command 'save as query'. If I open the 'filter'-query in design mode, I will see some 'tables' with names beginning with 'Lookup_'. Now is my question: Where are those tables defined?. They aren't listed with all the 'normal' tables (defined by myself), so where does Access define those tables and how can I see those definitions?

 

[RickSpr]

The "Lookup_..." items aren't real tables. They're actually the alias name of a kind of subquery that's built into the FROM clause of your saved filter query. They're inserted by Access when you filter by the value of a combo box or list box, and that value isn't in the control's bound column. That means it's not part of the form's recordset, so Access has to join the form's recordset with a lookup table (the one you name in the control's RowSource property) in order to match the value for filtering.

Access disables the View|SQL menu item for saved filter queries, so you can't look at the SQL syntax that creates these "tables" easily. However, you can see it by entering the following in the Debug or Immediate Window:
?CurrentDb.QueryDefs(&quot;<your saved query name>&quot.SQL


Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein