DLookUp (1 Viewer)

[PeteB]

Hi Guys


I use the DLookUp function as follows


=DLookUp("[MarrRecUnitPrice]","SearchCharges","ID=1") as a function in a field on a report where the field displays the record-unit-rate for records retrieved from a large dstabase (first database) where unit rates are stored in a small table in the same database.



Can I use the same or similar function to look up record-unit-rates in a second database where the table being refferenced is in the first database and if so, how would I need to amend the function?


Although I've raised queries on the forum previously, I remain a comparative amateur so please assume that I don't know much.


I would appreciate some help.


PeteB

 

[isladogs]

First of all you only need the [] if the field name has a space which yours doesn't
Suggest you wrap it in the Nz function to avoid null errors in case there are no matching records

=Nz(DLookUp("MarrRecUnitPrice","SearchCharges","ID=1"),"")

You can use exactly the same syntax on a table in a second database as long as that table is linked to your first database.
Once linked, Access treats tables the same as local tables for most purposes

 

[PeteB]

Thanks. I partly understand. The look-up table resides in database A but the report resides in database B so I wish to look-up from B to A. Apologies if I didn't explain very well previously.
PeteB

 

[isladogs]

You need to add the 'lookup table' as a linked table in database B and use that table in your function and/or report record source as appropriate.

Or import your report into database A if that's a better solution.

 

[PeteB]

Thanks very much, I'll give it a try.