Expression builder for Dlookup

[Cberry]

I am trying to create a query that will return the life expectancy factor from a table based on the calculated age in the query. The following is what I think is the pertinent information.

I have a query that calculates an age in years [MRD Age2] with the formula :Round(DateDiff("m",[IRA DOB],Now())/12,0)
I have a table [UDT] with three fields: primary key, Age of IRA Owner or Plan Participant, Life Expectancy.

I am trying to use another field in the query to return the factor based off of the MRD age by looking up age in the [UDT] table and returning factor.

I tried: UDT Factor: DLookUp([UDT]![Life Expectancy],[UDT],[UDT]![Age of IRA Owner or Plan Participant]=[MRD Age2]) but it returns an error.

Any help on the correct syntax/expression would be great.

Forgot to add that when I run the query it asks for a parameter for [UDT]![Life Expectancy], [UDT], and [UDT]![Age of IRA Owner or Plan Participant]

Thanks,

 

[alansidman]

Look at this syntax explanation

http://www.techonthenet.com/access/functions/domain/dlookup.php

I think that you are missing some quotation marks.
Alan

 

[Cberry]

Thanks for your response Alan. I had a bit of problem with the syntax of the criteria, but it's working now.

UDT Factor: DLookUp("[UDT]![Life Expectancy]","[UDT]","[UDT]![Age of IRA Owner or Plan Participant] = " & [MRD Age2])

 

[boblarson]

Just an FYI for you -

Normally in the field and the criteria you don't need to reference the table/query as you already told it where to look.

So this:
UDT Factor: DLookUp("[UDT]![Life Expectancy]","[UDT]","[UDT]![Age of IRA Owner or Plan Participant] = " & [MRD Age2])

Can be shortened down to:

UDT Factor: DLookUp("[Life Expectancy]","[UDT]","[Age of IRA Owner or Plan Participant] = " & [MRD Age2])