Usage of Dlookup

[commandolomo]

Hello All

I have a two tables, one a recordset (2k rows) - "Main" - and one a table of codes and names (15 rows) - Areas". Each table has a ID, which relates to the Name od an area held on the table to codes. I want to return the Name of the Area onto the query grid by looking up the ID from the Areas table - exactly the type of function Vlookup in Excel allows one to do.

I am attempting to run a query against the recordset, using a Dlookup to bring back the Name of an area - yet I am having problems. I have writtern this into a field in a Query

Expr1: DLookUp("[Area_Name]","[Areas]","[Prescirber_Mid_Code]"=[Areas]![Area_ID])

yet this does not work. I have tried other forms of syntax, but to no avail.

Is my assumption that Excel's Vlookup and Access' Dlookup do the same thing? If so, any ideas what may be wrong with my expression?

Cheers

 

[Len Boorman]

First

Are the tables normalised
Please describe table fields
what are primary and foreign keys

what exactly are you trying to achieve. (in words not code)

Len B

 

[Pat Hartman]

DLookup() is too inefficient to be used this way in a query. The proper technique is to join the main table to the lookup table. Then you can choose the columns you need from each table. Joins are optimized so this is the fastest way to do the job.