DLookup Query

[Pure Salt]

When I create a DLookup it shows the correct answer, but if the data is amended in the main table (eg haulage rate) it updates the DLookup answer.



Is there a way you can do a DLookup but not have the answer linked to the table?

[June7]

No. That's what domain aggregate functions are for, to summarize data in table. What do you mean by amended - new record or edit existing record or both?

[Pure Salt]

I have a table containing current haulage rates, so these are amended when we have had an increase. Within the order form I select a haulier & would like it to show the current rate - though if the rates were increased I would not want the field to update.

Hope that makes sense!

[rpeare]

No it doesn't. I'm guessing that what you really want to do is have rates that apply to a specific date range. If that's the case your rates table may have to change to include a start date/end date so that you can dlookup by a specific date.

[June7]

Your options are to:

1. have a single record for each halier in rates table and save the haulage rate with every order record so that when the rate changes, the existing order records are not changed

2. have multiple records for each hailer's rates and save record ID or as rpeare suggested, include date range fields and use them as additional criteria in the DLookup

I recommend avoiding domain aggregates in queries and on forms/reports whenever possible. They can be slow.

[Pure Salt]

Thank you June7