Hi

I am trying to update a table using an update query. I want to lookup a table containing rates to apply according to the specified criteria in the Dlookup statement. I am doing something wrong as all I get is a null response.

The lookup table is Distribution-Rates and the target table is Distribution Publications. The target field is Amount. I want to select an item in the Rates Table that matches the Publication IDs in both tables AND the Year of the Effective date in the rates table and the Year of the startdate in the Publications table.

I have used the following in the "Update to" for the field Amount of the query based on the Publications Table:

DLookUp("[SARate]","Distribution-Rates","([PubID] = [Distribution Publications]![DistPub]) AND (Year([Effective]) = Year([Distribution Publications]![StartDate]))")

I have checked that all fields have matching properties.

Rates table fields used above are SARate, PubID and Effective.
Publications fields are Amount, DistPub and Startdate.

BTW, I tried using a select query and adding a calculated expression using Dlookup which gave an error that Access couldnt find the specified Publication fields.

It seems that the Publications references are failing on the update query because I tried putting a numeric value for DistPub and it worked.

I use this Dlookup in a form maintaining the publications table and it works perfectly well. Now I have to append new records and need ensure that this information is included.

Regards

Tony Randell