Dlookup in an Update Query Failing

[trandel]

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

[GolferGuy]

Tony,
Whatever fields being used in the DLookup that are coming from the table being updated need to be OUTSIDE the double quotes ("...") Right now you are passing the field to be used in the lookup function, but you need to be passing the DATA. The only way to pass the data while this DLookup is within a query is to not have the reference to the field inside of the double quotes. ( I just said the same thing twice, just in two different ways.) I believe the following will work:

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

[trandel]

Hi GolferGuy

Thanks for your reply.

Tried it out but I got a message - "The expression you entered has too many closing parentheses."

I used the following as suggested:

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

I tried some combinations but received 'invalid strings' message.

Any ideas?

Regards

Tony Randell

[trandel]

Hi GolferGuy

Got it - the closing bracket for the compare of PubId was not right. I moved it ") after the & and before the AND:

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

Works just great.

Thanks for your help.

Regards

Tony Randell

[GolferGuy]

Tony, good job on finding that extra ")" Glad it worked for you.