Use calculate age to write other fields automatically
in [Forms]
|
Prev: cancel form unload
Next: MDE in A2003, A2007
From: Diego via AccessMonster.com on 11 May 2010 04:56 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 KenSheridan wrote: >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] >>>>>>>>>> Best regards >>>>>>>>>> Diego -- Message posted via http://www.accessmonster.com
From: KenSheridan via AccessMonster.com on 11 May 2010 06:44 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 Diego wrote: >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] >>>>>>>>>>> Best regards >>>>>>>>>>> Diego -- Message posted via http://www.accessmonster.com
From: Diego via AccessMonster.com on 11 May 2010 10:34 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 KenSheridan wrote: >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] >>>>>>>>>>>> Best regards >>>>>>>>>>>> Diego -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201005/1
From: KenSheridan via AccessMonster.com on 11 May 2010 13:24 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 Diego wrote: >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] >>>>>>>>>>>>> Best regards >>>>>>>>>>>>> Diego -- Message posted via http://www.accessmonster.com
From: Diego via AccessMonster.com on 12 May 2010 08:37
Interesting ! I will test this new function in order to propose to the sport club. thank you very much again Diego KenSheridan wrote: >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] >>>>>>>>>>>>>> Best regards >>>>>>>>>>>>>> Diego -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201005/1 |