Queries in a listbox

[SeRene]

Hi, I need to make use of a listbox to display ALL the
queries in my
database. Is there any way i can do this??

Another scenario is.. i need to know how to display only
the queries i
want, to appear in the listbox.

Thank You!

 

[Graham R Seach]

Set the listbox's RowSource to:
SELECT [Name] FROM MSysObjects WHERE [Type] = 5 AND Flags = 0

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Arvin Meyer]

Hi, I need to make use of a listbox to display ALL the
queries in my
database. Is there any way i can do this??

Set the listbox's rowsource type to table/query and the rowsource to:

SELECT MSysObjects.Name FROM MSysObjects WHERE MSysObjects.Name Not Like
"~sq*" AND MSysObjects.Type=5

Not Like "~sq*" excludes any select statements for combos and listboxes.

Another scenario is.. i need to know how to display only
the queries i
want, to appear in the listbox.

You'll need to use the above query with some criteria. If you used a naming
convention on all the queries you wanted to display like qry_MyQuery, where
the underscore was part of the name for only the queries you wanted in a
list box, you might add a column to the above query like:

Display: IIf(Left([Name], 4) = "qry_", [Name],"")

and then use a criteria on that column like:

<>""
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[SeRene]

Hi,

Umm.. i think i got the 1st part.. which is to display all
my queries in the listbox. However, for the second part, i
don't really understand the part: Display: IIf(Left
([Name], 4) = "qry_", [Name],"")
What is the above statement supposed to appear in??

and then use a criteria on that column like:

<>"" <---- and what does this means?

Sorry!! I am not that IT-savvy, don't really understand
all these codes!

 

[Arvin Meyer]

Hi,

Umm.. i think i got the 1st part.. which is to display all
my queries in the listbox. However, for the second part, i
don't really understand the part: Display: IIf(Left
([Name], 4) = "qry_", [Name],"")
What is the above statement supposed to appear in??

and then use a criteria on that column like:

<>"" <---- and what does this means?

Sorry!! I am not that IT-savvy, don't really understand
all these codes!

1. Start the name of every query you want to display with "qry_"

2. Paste the following (all on one line) into a column in a query:

Display: IIf(Left([Name], 4) = "qry_", [Name],"")

3. Use the following in the criteria box:

<>""


What that does is create a column in your previous query where everything is
either empty ("") or the query name. Using: Not equal to an empty string:

<>""

eliminates all the values which don't start with "qry_" from the query
(select statement) which fills the list box.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[SeRene]

Hi, I have tried this method already. However, it is not
working and i wonder where i went wrong!!

1. I renamed all the queries which i want to display with
a "qry_" at the front.
2. Create a new query with the field as --> Display: IIf
(Left([Name], 4) = "qry_", [Name],"") and the criteria as -
-> <>""
3. When i save and wanted to view it, there is a Parameter
value.
4. Checked my listbox and all the unwanted queries are
still listed in the lstbox.