Newbie with DLookup question.

[Templar333]

Hi All,

I'm new to Access so please bear with me. I'm creating a query (called PRDB) from a table (Members) which holds information about members. One of the field's is the 'Date of Birth'. One of the columns in the query is to be used to calculate benefits. The calculation to be perform is based on the YEAR portion of the 'Date of Birth' in reference to another table (lookup?).

For e.g., if member A's 'Date of Birth' list 1934, then the lookup table, 2002CovComp (which has only 2 fields, 'DOB' & 'Compensation'), has a corresponding year in 'DOB' and the matching 'COMPENSATION'.

What I've done so far in the query is to make a column, DOB2, which uses DatePart to recognise the year part of 'Date of Birth' :

DOB2: DatePart("yyyy",[Active Members]![DATE OF BIRTH])

When I run the query, it works easily but on the next column, I want the matching 'COMPENSATION' from the lookup table to show, so this is what I typed in but of to no avail:

DLookUp("[2002CovComp]![COMP]","2002CovComp","[DOB2]=" "& DOB &&quot

I've tried variations of this but I'm quite sure I'm barking up the wrong tree.

My sincere thanks in advance for any help provided.

Helm.

 

[jacksonmacd]

(aircode -- I *think* this will work)

Remember, the three parts of Dlookup are: the field to return, the table to look it up in, and the criteria to apply. Therefore, you want something like:

dlookup("compensation", "2002CovComp ", "DOB = " & DatePart("yyyy",[DATE OF BIRTH]))

You also have to be careful of the fieldtype of the DOB field. I presume it is an Integer. If it's DateTime, then you need to deal with "#" to properly delimit the criteria.

HTH

 

[Templar333]

jacksonmacd,

Thank you very, very much for your help. It works! What a relieve! This is the great thing about forums like this. It offers novices like myself a chance at understanding things with the help of pros like yourself. Once again, my sincere thanks.