Prev: MouseUP is firing when Form is Loaded
Next: update cascading forms
From: Diego via AccessMonster.com on 10 May 2010 06:16
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201005/1

From: Douglas J. Steele on 10 May 2010 07:07
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))

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

"Diego via AccessMonster.com" <u28514(a)uwe> wrote in message
news:a7ca407eaee68(a)uwe...
>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
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201005/1
>


 | 
Pages: 1
Prev: MouseUP is firing when Form is Loaded
Next: update cascading forms