Problem in a query with DLookUp

[Diego via AccessMonster.com]

I have a query that take the Data_Nascita value from table Anagrafica.
In Expr1 i calculate the value in year, no problem.
Then i am tring to get the correct value of Categoria related to age value i
have the problem.
With this code a get always the first value in the table that is not
correlated to the age calculation and obviously is not correct.

SELECT Anagrafica.Data_Nascita, DateDiff("yyyy",[Data_Nascita],Date())-IIf
(Format([Data_Nascita],"mmdd")>Format(Date(),"mmdd"),1,0) AS Expr1, DLookUp
("Categoria","Anni_Categoria_Calcio_LND",[Expr1]) AS Categoria
FROM Anagrafica;

The table Anni_Categoria_Calcio_LND is like

Anni Categoria Descrizione
5 Cat_A Description A
6 Cat_B Description B
7 Cat_C Description C
.......
18 ...... ...............

The value of Categoria in the DLookUp is always the first, Cat_A also if the
age is 6, 7 ecc

Any idea ?
Thank you in advance
Diego

 

[Douglas J. Steele]

By design, DLookup will only return a single value, and you really don't
have any control over which value it will return if there are multiple that
match the criteria.

However, your DLookup statement doesn't look correct anyhow: [Expr1] is an
Alias for the Age calculation you've performed: it cannot be used by itself
as a criteria. If nothing else, you need something like "[Age] = " &
[Expr1], although to be honest I think you'll need to repeat the calculation
("[Age] = " &
DateDiff("yyyy",[Data_Nascita],Date())-IIf(Format([Data_Nascita],"mmdd")>Format(Date(),"mmdd"),1,0))