Using lookup fields in criteria

When you define a criterion for a query, you tell Access what you are looking for - either by entering a value or by using a logical expression. However, if you use a criterion to limit the number of records displayed from a lookup field, you have to figure out exactly what value you want to find - and that may not be the value you see in the table.

How about an example? You want to find orders for the Budget MP3 Player. The Order Details table stores this data. Notice that the ProductID field is a lookup field - it displays values from the Product Name field of the Products table, but stores the values from the Products table primary key field, which is ProductID.

Because the ProductID field in the Order Detail table is a lookup field, the criteria need to refer to the value that is stored in the field, not the value that displays. The value that is stored is the primary key field from the Products table. The value that displays is the product name. If we enter Budget MP3 Player for the ProductID criterion and then try to view the datasheet, we see a Data type Mismatch in Criteria Expression error message. We need to go back to the Products table and find the ProductID number for the Budget MP3 Player. (Remember: A lookup field always stores the primary key field.)

Queries with multivalue lookup fields

Multivalue lookup fields make queries a little more complicated. The question is whether you want to display the complete multivalue field with each value separated by a comma, or put each value on its own line in the query datasheet. If you want to do complicated analysis with multivalue fields, you might want to reconsider your database design, and add tables and fields in order to save the same data without the multivalue field.

Although a multivalue lookup field seems cumbersome, it can still give you the results you want if you simply have your query display the multiple values separated by commas. If you want to deconstruct your data some - and ensure each value in a multivalue field has its own line - you'll need to add the Value property to the field name. Here's how: Instead of just multivalue field name in the query grid, enter Multivalue Field name.Value.

In addition to the special instructions about multivalue fields, remember the caveat about lookup fields: The value you see may not be the value that is actually stored. (Fortunately, the preceding section offers tips on using lookup fields in your criteria.)