Query on Lookup?

[burrelly]

I have a table called Employees within that there is a field employmentstatus the vales that can be selected are from a lookup on another table. All that work correctly until i come to query the Employees table.

I am wanting to query all employees with the criteria of "Active" but as it is a lookup the field format is number and I am getting a mismatch error.

How can I get around this?

I need the lookup so the client can easily add or remove status types.

 

[boblarson]

How can I get around this?

Get rid of lookups at table level!

See here for why:
http://www.mvps.org/access/lookupfields.htm

I need the lookup so the client can easily add or remove status types.

The client should not be working in the tables directly. Use a FORM.

 

[georgedwilkinson]

You said this is in a query, right? Just join the lookup table in the query and select the correct column within the lookup table.

 

[burrelly]

You said this is in a query, right? Just join the lookup table in the query and select the correct column within the lookup table.

I am sorry but I do not understand what you mean?

 

[georgedwilkinson]

I realize how frustrating this can be because I went through the same learning curve when I started using Access in earnest.

You can add a lookup (using a combo-box, for instance) to a field in a query.

As Bob pointed out, and as you SEEM to be practicing, you shouldn't do lookups at the table level. Since you mentioned you are doing this in a query, my assumption is that you know how to create a lookup at the table level (thus prompting Bob's response) but do not know how to create a lookup at the query level.

To get to the point that you can force a field in a query to be a combo box for looking up the value of your number, right click on the field in the QBE and select properties, go to the Lookup tab, and select Combo Box in the DDL. Set up the combo-box the way you did in the table level lookup you created.

If you need additional information, let us know, but please provide a lot more detail about the table and field names involved.

 

[burrelly]

I realize how frustrating this can be because I went through the same learning curve when I started using Access in earnest.

You can add a lookup (using a combo-box, for instance) to a field in a query.

As Bob pointed out, and as you SEEM to be practicing, you shouldn't do lookups at the table level. Since you mentioned you are doing this in a query, my assumption is that you know how to create a lookup at the table level (thus prompting Bob's response) but do not know how to create a lookup at the query level.

To get to the point that you can force a field in a query to be a combo box for looking up the value of your number, right click on the field in the QBE and select properties, go to the Lookup tab, and select Combo Box in the DDL. Set up the combo-box the way you did in the table level lookup you created.

If you need additional information, let us know, but please provide a lot more detail about the table and field names involved.

Ok I HGave that working in the query but when I add the field to a form from the Query it is just a normal text box. How do I force it to be a combo box on the form?

 

[georgedwilkinson]

Right click it in design view and select change to combo box.