DLookup Syntax - My bad?

[commandolomo]

Hey y'all

Can anyone see what may be wrong with the following Dlookup syntax?

I have a field in a query, MID, and want to return from another table a corresponding name that matches the MID code. I have constructed the below, but the query expression builder does not like it -

DLookUp("([Area])",[Area Lookup],"[Mid]='"[Area Lookup]!

"'")

FYI, "Area", "MID" and "Code" are all text values.

Any ideas most gratefully received!

Cheers

 

[FoFa]

Try:
DLookUp("[Area]","[Area Lookup]","[Mid]='" & [Area Lookup]!

 & "'")

 

[commandolomo]

Fofa

Many thanks for the prompt reply, but I am now getting a Enter Parameter Value input box for Area Lookup!Code, and irregardless of the value input, an error message then appears saying that "The expression you entered as a query parameter produced this error: 'Microsoft Office Access can't find the name 'Mid' you entered in the expression'"

MID is a calculated value that is created when the query is run - should this affect the Dlookup? Screenshot of query is attached.

Cheers for the assistance

 

[FoFa]

You have to reference a field in the [Area Lookup] table, if Mid is not contained in that table, you would get this error. Use a column in the [Area Lookup] table in your criteria for the dlookup.

 

[commandolomo]

Cheers FoFa

Maybe I am misunderstanding the purpose of Dlookup. What I want to do is to return from Team Lookup the name of the area as based on the code provided from the MID value.

For example, the Query that contains the MID value has over 3K records, and so for each value the MID expression brings back a single letter, A through to D. Each of these values correspondes to a value in the Team Lookup table - for example

Code Area
A - North
B - East
C - South
D - West

ergo I am attempting to use the Dlookup to bring back the Area name based upon what the MID value (calculated in the query) is.

Am I way off track using Dlookup to do this? I hope the above ramble is vaguely coherent!

Cheers

 

[FoFa]

have you tried LEFT JOIn instead of DLOOKUP?

LEFT JOIN [AREA Lookup] on Mid([Prescriber],3,1) =