DLookUp Not Returning Anything

[Bkper087]

Not real sure what I'm doing wrong. I am trying to match the field in [MembershipT]![Rank] to [Description] in [lktPositionCodesT]. I want the [Points] field to be the value shown. When I run this in a query nothing is returned. Any ideas?



DLookUp("[Points]","[lktPositionCodesT]","[Description]='[MembershipT]![Rank]'")

[ssanfu]

Not sure what "lktPositionCodesT" is, but maybe this will help you.

Three things:
1) You can't refer directly to a field in a table.
2) You have to concatenate the value in the function with appropriate delimiters
3) The DLookup() function will return one value, if found.

Here is a scenario:
You had a form named "Members"
On that form, there is a control named "Rank"
and you have a control to display the points,

you would use

DLookUp("[Points]","lktPositionCodesT","[Description]='" & Me.Rank & "'")

or you could refer to a control on a different form

DLookUp("[Points]","lktPositionCodesT","[Description]='" & Forms!AnotherForm.Rank & "'")

[orange]

It would be easier to understand if you showed us your table design, and some sample data.
We only know what you tell us and there isn't much to go on here. I hope Steve has guessed correctly, but it is helpful to readers when you tell us about what you're trying to accomplish, the data types involved, and sample data.

[John_G]

Its a simple syntax error, I think - you have [MembershipT]![Rank] inside the quotes, and it should not be.

Try this:

DLookUp("[Points]","[lktPositionCodesT]","[Description]='" & [MembershipT]![Rank] & "'")

(additional character in red). If [MembershipT]![Rank] is numeric, remove the single quotes.

[Bkper087]

Thanks for the help. ssanfu, your code helped me figure it out. I still don't really understand what is going on with this but it is working now. Partly what confused me is using a single tick mark ' vs a double " in this equation. If anyone cares to explain that part it may help me and others out in the future.

[orange]

Text/string values have to be enclosed in quotes.
So in the example by John

DLookUp("[Points]","[lktPositionCodesT]","[Description]='" & [MembershipT]![Rank] & "'")

The DLookup syntax is http://www.techonthenet.com/access/f...in/dlookup.php

In John's example the criteria expression is a string:
So the whole criteria expression has to be enclosed in quotes,
and because you want the value of MembershipT!Rank to be rendered as a string value
it has to be within quotes

"[Description]='" & [MembershipT]![Rank] & "'"
The single quote after the = sign is the method to put a single quote before the rendered MembershipT!Rank value, and the "'" is the way you add the trailing quote around the rendered MembershipT!Rank.

By rendered value I mean, Access has to find the named field MembershipT!Rank and then get its value and insert it into your DLookup function.

For more info on delimiters see
http://allenbrowne.com/binary/Access...Chapter_07.pdf

Good luck.