DLookUp Help Please (Very New to Access)

[TheGlovner]

So I've just started trying to get into MS Access (2010) to solve some issues in our company's processes.

Issue being is that a large number of spreadsheet tools they have run off the same data, but the data is held multiple times across each of the tools it is used in. It's all Tax Year specific data so there is a huge exercise each year when these various rates/thresholds change and they then go about retesting each tool from first principles.

Seems obvious to me that if we can extract the data and hold/update completely independent of the tool then we can save ourselves a fair bit of time every year.

So right now I'm busy consolodating all the data between tools into various Access Tables.

I'm starting to get into the more functional data lower down now and I'm trying to create one of the fields as an expression.

Here is the text from the query builder (had a look at it in the SQL window but I'm used to DB2 z/os syntax so it isn't making complete sense to me in there at first glance):

ProbablityOfDying: DLookUp("Mortality" & CStr("[AnnuityCalculationsT]![BirthYear]"),"[DOBMortalityMalesT]","[AnnuityCalculationsT]![AtAge]=[DOBMortalityMalesT]![Age]")

So I have a table "DOBMortalityMalesT" which holds various death probabilities based on the persons Age (PK - Field/Column 1) and their date of birth (the rest of the columns from left to right after age named "MortalityXXXX" where XXXX is each year from 1918 to 2015.

The other table is AnnuityCalculationsT which has columns BirthYear and AgeAt.

So the BirthYear column prefixed with the string "Mortality" will equal one of the Field Names from "DOBMortalityMalesT"

So I'm trying to get

The field name "Mortalityxxxx" (xxxx being replaced by the value in column BirthYear).
From the table "DOBMortalityMalesT"
Where "DOBMortalityMalesT.AgeAt" is equal to AnnuityCalculationsT.Age

Not sure if the problem is being created by the fact I'm trying to create a field name from two pieces of data (one from the database and a string) and apply the dlookup against this.

 

[ranman256]

I wouldn't use DLOOKUP in a query, it sloooows it down.
if you use a query use tables to do the calcs.

 

[TheGlovner]

I'll try switching it over, cheers for the tip, hopefully it magically address the issue I was having (as usually happens when you try it a different way).

Good with excel, very new to Access and trying to not think only in spreadsheet mode anymore. Not easy