Parameter queries that look to list box fields

[Guest]

Hello, I'm trying to build a parameter query and need help. I have a facility table that has a list of facility names. I have a employee table that lists employees and contains a list box field for "facility" that looks to the facilty table for the options in the list box. If I try to build a parameter query of the employee table, where the criteria is [Department:] in the facility field, the query returns no records. What do I need to do to have the parameter query to recognize the list box selections in the employee table?

 

[Steve Schapel]

Bill,

This is one of the dangers of using a Lookup field in your tables, and
you are very strongly advised not to do this. I suspect what is
causing your problem is that the data that is actually in your table
is not shown, and what is shown is not actually in your table. Does
the Facility table contain at least two fields, being an ID type
primary key field, and a Description type field? If so, try this...
look at the Facility table and see what the ID of a particular
Department is. Then, try to run your Parameter Query, and at the
parameter prompt, type in the ID for the department you want, rahter
than what I presume you are doing now which is to enter the department
name, and see if that produces the goods.

- Steve Schapel, Microsoft Access MVP

 

[Guest]

Steve, you are correct. If I enter the ID field number it returns the Department name and the query works. I was hoping to use the list box the ensure correct data entry format, and because we only have 10 facilities to choose from. Is there a way to correct it so the I don't have to enter the ID field to get the query to function the way I would like?

 

[Steve Schapel]

Bill,

It is a good idea to use a listbox or combobox on the form for your
data entry. This is a different issue from the use of a Lookup field
in your table. For more information on this, see
http://www.mvps.org/access/lookupfields.htm

You can try one of these ideas, both of which should work well...
- Include both your tables in the query, joined by the fields with the
data in common, i.e. presumably the ID field in the Facility table and
the Department field in the Employees table. Then put the
FacilityName field from the Facility table into the query design grid,
and put your [Department:] parameter prompt in the criteria of this
field.
- Instead of using a parameter query, put an unbound combobox or
listbox on a form, with the rowsource set to the Facility table, and
select the department you require here. Then, in the query criteria,
insteaqd of the parameter prompt, refer to the listbox or combobox,
using syntax such as [Forms]![NameOfForm]![NameOfList/Combo]

- Steve Schapel, Microsoft Access MVP

 

[John Vinson]

What do I need to do to have the parameter query to recognize the list box selections in the employee table?

You can't.

You can, however, use a Form with a listbox on it as a criterion; use

=[Forms]![NameOfForm]![NameOfListbox]

as a criterion.