?

[Mra3134]

Hi there,



i want to create a query which reads data from a record. for example, suppose i have a table with three fields: [id], [department], [employee]. department field has a lookup type and dets its data from another table. employee field also has the lookup type. but there are a lot of employees and i want to filter them by the value which i had entered in the department field in that particular record. but i dont know how to do it. please help me!

tnx,
MRA

[pbaldy]

Here's one way:

BaldyWeb wherecondition

A parameter query would be another, referring to the form control in the criteria of the query.

[Micron]

I should mention first that table lookup fields are considered poor practice - unless maybe you're one of a very few that would be using the db, and the data in those fields is rarely changed, and the lookup is for doing things like selecting from a limited controlled list (again, once in a blue moon and by few people). If you can't say that, save yourself some grief and redesign.

If you insist or just want to learn how to drill down to the value of a lookup field, your query criteria needs to make use of the .value property of these types of field, such as WHERE Empl.Value = forms!myFormName.myControlName where Empl is the name of the field in your query design view. The problem is, what you see in the table is not really there - it is the result of hidden pointers to the source. What's really in the table is the value of the pointer. You are only going to have more trouble with this if what you're doing doesn't meet the test I outlined.

P.S. a more descriptive title for your post helps people decide on whether or not to pursue it, and helps in future searches.

[pbaldy]

More on what Micron is talking about, which I fully agree with:

http://access.mvps.org/access/lookupfields.htm

[Mra3134]

Here's one way:

BaldyWeb wherecondition

A parameter query would be another, referring to the form control in the criteria of the query.

Tnx it works! but i cant use it in the lookup field. yet it asks me the value in a combo box.

[Mra3134]

okay so what is the best substitution for Lookup function?

[pbaldy]

Can you attach the db here?

[CJ_London]

sounds like you are taling about cascading combos. But they will only work in a form, not a table our query