Use calculate age to write other fields automatically

[Diego via AccessMonster.com]

Probably i have done something of wrong
When i run the update query it opened a little form that ask VBA_Date.
I do nothing, only click ok on the little form and the query works fine and
updated the table.

What is my error?

Let me know
thanks
Diego

Diego:

If you want to update the Descrizione and Categoria field’s values you’d use
an update query along these lines:

UPDATE YourTable
SET Descrizione =
DLookup("Descrizione","Anni_Descrizione", "Anni = " &
DateDiff("yyyy", Data_Nascita, Date()) -
IIf(Format( Data_Nascita, "mmdd")

Format(VBA.Date, "mmdd"), 1, 0)),

Categoria =
DLookup("Categoria","Anni_Descrizione","Anni = " &
DateDiff("yyyy", Data_Nascita, Date()) -
IIf(Format( Data_Nascita, "mmdd")

Format(VBA.Date, "mmdd"), 1, 0));

To return the values in computed columns use the same expressions.

Ken Sheridan
Stafford, England

Hi Ken
since your suggestion works fine i have a new question

[quoted text clipped - 21 lines]

 

[KenSheridan via AccessMonster.com]

Diego:

Mea culpa!

If you look at my last post you'll see that the expression uses Date(),
rather than VBA.Date, but I forgot to change it in two places. The latter is
fine in code, but not in a query, which is why it prompts for the parameter.
It should have been:

UPDATE YourTable
SET Descrizione =
DLookup("Descrizione","Anni_Descrizione", "Anni = " &
DateDiff("yyyy", Data_Nascita, Date()) -
IIf(Format( Data_Nascita, "mmdd")

Format(Date(), "mmdd"), 1, 0)),

Categoria =
DLookup("Categoria","Anni_Descrizione","Anni = " &
DateDiff("yyyy", Data_Nascita, Date()) -
IIf(Format( Data_Nascita, "mmdd")

Format(Date(), "mmdd"), 1, 0));

I'm still not convinced of your need to store the values in the table,
however, as you can return them in computed columns in a query at any time
and they'll always reflect the current age of the young person in question.
If the Descrizione and Categoria columns are removed from the table the query
would be like this:

SELECT *,
DLookup("Descrizione","Anni_Descrizione", "Anni = " &
DateDiff("yyyy", Data_Nascita, Date()) -
IIf(Format( Data_Nascita, "mmdd")

Format(Date(), "mmdd"), 1, 0)) AS Descrizione,

DLookup("Categoria","Anni_Descrizione","Anni = " &
DateDiff("yyyy", Data_Nascita, Date()) -
IIf(Format( Data_Nascita, "mmdd")

Format(Date(), "mmdd"), 1, 0)) AS Categoria;

Ken Sheridan
Stafford, England

Probably i have done something of wrong
When i run the update query it opened a little form that ask VBA_Date.
I do nothing, only click ok on the little form and the query works fine and
updated the table.

What is my error?

Let me know
thanks
Diego

Diego:

[quoted text clipped - 23 lines]

 

[Diego via AccessMonster.com]

Ok
it seems that both are working
Related to your doubts you are right but this is a long long discussioni with
sport club.

They want to have a fix situation about the category of the boys, since the
registration at the beginnig of the sport year (september) in the Italian
Soccer Federation is fixed. It is not possible to change the category during
the year. If i do not fix the category (write in the table) at the beginning
(september) it should be possible that during the year (after december) some
boys have the birthday and consequently they can have a different category
respect the beginning.

If i write category in the table the category is fixed for all the sport year
for everyone, the Soccer Federation is "happy", and the Sport Club has
situation under control. At the beginnig of the next sport year, if the boys
are the same it is enought to update the table to have a new updated
situation.

Anyway again thank you

diego

Diego:

Mea culpa!

If you look at my last post you'll see that the expression uses Date(),
rather than VBA.Date, but I forgot to change it in two places. The latter is
fine in code, but not in a query, which is why it prompts for the parameter.
It should have been:

UPDATE YourTable
SET Descrizione =
DLookup("Descrizione","Anni_Descrizione", "Anni = " &
DateDiff("yyyy", Data_Nascita, Date()) -
IIf(Format( Data_Nascita, "mmdd")

Format(Date(), "mmdd"), 1, 0)),

Categoria =
DLookup("Categoria","Anni_Descrizione","Anni = " &
DateDiff("yyyy", Data_Nascita, Date()) -
IIf(Format( Data_Nascita, "mmdd")

Format(Date(), "mmdd"), 1, 0));

I'm still not convinced of your need to store the values in the table,
however, as you can return them in computed columns in a query at any time
and they'll always reflect the current age of the young person in question.
If the Descrizione and Categoria columns are removed from the table the query
would be like this:

SELECT *,
DLookup("Descrizione","Anni_Descrizione", "Anni = " &
DateDiff("yyyy", Data_Nascita, Date()) -
IIf(Format( Data_Nascita, "mmdd")

Format(Date(), "mmdd"), 1, 0)) AS Descrizione,

DLookup("Categoria","Anni_Descrizione","Anni = " &
DateDiff("yyyy", Data_Nascita, Date()) -
IIf(Format( Data_Nascita, "mmdd")

Format(Date(), "mmdd"), 1, 0)) AS Categoria;

Ken Sheridan
Stafford, England

Probably i have done something of wrong
When i run the update query it opened a little form that ask VBA_Date.

[quoted text clipped - 12 lines]

 

[KenSheridan via AccessMonster.com]

Diego:

I thought that was probably the case, but even so you can still compute a
player's current description and category at the time of the start of the
current sporting year. First you need a little function to return the start
date of the sporting year:

Public Function SportYearStarts(intMonth As Integer, intDay As Integer) As
Date

Dim intYear As Integer

If Format(VBA.Date, "mmdd") < _
Format(intMonth, "00") & Format(intDay, "00") Then
intYear = Year(VBA.Date) - 1
Else
intYear = Year(VBA.Date)
End If

SportYearStarts = DateSerial(intYear, intMonth, intDay)

End Function

Then when using an expression to return the category or description for a
player, instead of using VBA.Date function in code or Date() in a query you'd
use SportYearStarts(9,1). The 9 and 1 are the month and day of month when
the year starts, so the same function could be used to compute the current
start date of the current sporting year for a sporting year starting on any
date.

This way, whenever it is within the year, the player's age at the start of
the sporting year will be computed, so there is no need to update a table
every September; the player's age and thus their category and description
will be automatically updated each year on 1st September.

Strictly speaking the 9 and 1 should be stored as values in a table and those
values used when calling the function, rather than simply entered in the
expression or query as literal values. The 9 and 1 are data, and it’s a
fundamental principle of the relational database model that data are only
stored in tables and in no other way. It was in fact Codd's Rule 1 (the
Information Rule) when he first proposed the relational model for databases
back in 1970. At a practical level its far simpler to update values in a
table than change the code for expressions or queries.

Anyway, I'll leave you to decide just how far you want to go in the interests
of relational purity.

Ken Sheridan
Stafford, England

Ok
it seems that both are working
Related to your doubts you are right but this is a long long discussioni with
sport club.

They want to have a fix situation about the category of the boys, since the
registration at the beginnig of the sport year (september) in the Italian
Soccer Federation is fixed. It is not possible to change the category during
the year. If i do not fix the category (write in the table) at the beginning
(september) it should be possible that during the year (after december) some
boys have the birthday and consequently they can have a different category
respect the beginning.

If i write category in the table the category is fixed for all the sport year
for everyone, the Soccer Federation is "happy", and the Sport Club has
situation under control. At the beginnig of the next sport year, if the boys
are the same it is enought to update the table to have a new updated
situation.

Anyway again thank you

diego

Diego:

[quoted text clipped - 41 lines]

 

[Diego via AccessMonster.com]

Interesting !
I will test this new function in order to propose to the sport club.
thank you very much again
Diego

Diego:

I thought that was probably the case, but even so you can still compute a
player's current description and category at the time of the start of the
current sporting year. First you need a little function to return the start
date of the sporting year:

Public Function SportYearStarts(intMonth As Integer, intDay As Integer) As
Date

Dim intYear As Integer

If Format(VBA.Date, "mmdd") < _
Format(intMonth, "00") & Format(intDay, "00") Then
intYear = Year(VBA.Date) - 1
Else
intYear = Year(VBA.Date)
End If

SportYearStarts = DateSerial(intYear, intMonth, intDay)

End Function

Then when using an expression to return the category or description for a
player, instead of using VBA.Date function in code or Date() in a query you'd
use SportYearStarts(9,1). The 9 and 1 are the month and day of month when
the year starts, so the same function could be used to compute the current
start date of the current sporting year for a sporting year starting on any
date.

This way, whenever it is within the year, the player's age at the start of
the sporting year will be computed, so there is no need to update a table
every September; the player's age and thus their category and description
will be automatically updated each year on 1st September.

Strictly speaking the 9 and 1 should be stored as values in a table and those
values used when calling the function, rather than simply entered in the
expression or query as literal values. The 9 and 1 are data, and it’s a
fundamental principle of the relational database model that data are only
stored in tables and in no other way. It was in fact Codd's Rule 1 (the
Information Rule) when he first proposed the relational model for databases
back in 1970. At a practical level its far simpler to update values in a
table than change the code for expressions or queries.

Anyway, I'll leave you to decide just how far you want to go in the interests
of relational purity.

Ken Sheridan
Stafford, England

Ok
it seems that both are working

[quoted text clipped - 24 lines]