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 3 May 2010 11:58 Just to clarify I have not problem to see the age calculation in the form Anni. It works fine, infact in the unbonded fields i see the correct age. My problem is use the age calculation to write Categoria and Descrizione fields depending from the age value. I have also to test this code in the Data_Nascita to write the correct value in the Categoria adn Description but it does not works. I see the correct Anni value, but the Categoria and Description value are empty. Private Sub Data_Nascita_AfterUpdate() Dim age As Integer age = DateDiff("yyyy", [Anni], Now()) + (Int(Format(Now(), "mmdd") < Int (Format([Anni], "mmdd")))) If age = 12 Then Me.Categoria = "P_1" Me.Descrizione = "Pulcini 1_A" ElseIf age = 13 Then Me.Categoria = "P_2" Me.Descrizione = "Pulcini 2_A" End If End Sub Al Campagna wrote: >Diego, > You <clipped> your Anni formula from the thread. Please only clip >non-essential text from previous posts. >*=DateDiff("yyyy",[Data_Nascita],Date())-IIf(Format([Data_Nascita],"mmdd") >*>Format(Date(),"mmdd"),1,0) > > Whenever you use suggested code, and have a problem, Cut & Paste that >code >into your reply. That way we can say, yes or no, that your code is OK. > I agree that the problem is probably the Anni calculation. > > Try this Anni... >=DateDiff("yyyy",[Anni],Now())+(Int(Format(Now(),"mmdd")<Int(Format([Anni],"mmdd")))) > > I tested this, and it works with my code... >> It does not works >> the problem seems to be in the age calculation >[quoted text clipped - 29 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 3 May 2010 14:03 Diego: Why are you storing the Categoria and Descrizione values in fields in the table? These will change as the age changes, so surely you want them to reflect the current age. If you use unbound controls in the form (or in a report or as a computed column in a query) you can compute the values depending on the date of birth. Add the following two functions to a standard module in the database: Function GetCategoria(dtmData_Nascita As Date) As String Dim intAnni As Integer intAnni = DateDiff("yyyy", dtmData_Nascita, VBA.Date) - _ IIf(Format(dtmData_Nascita, "mmdd") _ > Format(VBA.Date, "mmdd"), 1, 0) If intAnni >= 12 And intAnni <= 14 Then GetCategoria = "P_" & intAnni - 11 Else GetCategoria = "Not within age range" End If End Function Function GetDesrcizione(dtmData_Nascita As Date) As String Dim intAnni As Integer intAnni = DateDiff("yyyy", dtmData_Nascita, VBA.Date) - _ IIf(Format(dtmData_Nascita, "mmdd") _ > Format(VBA.Date, "mmdd"), 1, 0) If intAnni >= 12 And intAnni <= 14 Then GetDesrcizione = "Pulcini " & intAnni - 11 & "_A" Else GetDesrcizione = "Not within age range" End If End Function Then in your form call the function's as the ControlSource properties of two unbound controls: =GetCategoria([Data_Nascita]) =GetDesrcizione([Data_Nascita]) You can do the same in a report or use the expressions for a computed control in a query. If there is some reason why you really do need to store the values in the fields you can still use the functions in the AfterUpdate event of the Data_Nascita control: Private Sub Data_Nascita_AfterUpdate() Me.Categoria = GetCategoria([Data_Nascita]) Me.Descrizione = GetDescrizione ([Data_Nascita]) End Sub Ken Sheridan Stafford, England Diego wrote: >Just to clarify >I have not problem to see the age calculation in the form Anni. >It works fine, infact in the unbonded fields i see the correct age. >My problem is use the age calculation to write Categoria and Descrizione >fields depending from the age value. >I have also to test this code in the Data_Nascita to write the correct value >in the Categoria adn Description but it does not works. I see the correct >Anni value, but the Categoria and Description value are empty. > >Private Sub Data_Nascita_AfterUpdate() > Dim age As Integer > > age = DateDiff("yyyy", [Anni], Now()) + (Int(Format(Now(), "mmdd") < Int >(Format([Anni], "mmdd")))) >If age = 12 Then > Me.Categoria = "P_1" > Me.Descrizione = "Pulcini 1_A" >ElseIf age = 13 Then > Me.Categoria = "P_2" > Me.Descrizione = "Pulcini 2_A" >End If > >End Sub > >>Diego, >> You <clipped> your Anni formula from the thread. Please only clip >[quoted text clipped - 16 lines] >>>>> Best regards >>>>> Diego -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201005/1
From: Mike Painter on 3 May 2010 14:44 Diego via AccessMonster.com wrote: > Hi All > i have a date field called Data_Nascita and and unbound field to > calculate the age (called Anni) with > =DateDiff("yyyy",[Data_Nascita],Date())-IIf(Format([Data_Nascita],"mmdd") >> Format(Date(),"mmdd"),1,0) > this works enoght. > > Now i want to write automatically other two fields (Categoria and > Descrizione) in the form related to age (Anni) value. > I start to write this code (after update of unbounded field) but it > does not work > > If Me.Anni = 12 Then > Me.Categoria = "P_1" And Me.Descrizione = "Pulcini 1_A" > ElseIf Me.Anni = 13 Then > Me.Categoria = "P_2" And Me.Descrizione = "Pulcini 2_A" > ElseIf Me.Anni = 14 Then > Me.Categoria = "P_3" And Me.Descrizione = "Pulcini 3_A" > End If > > I have not any error, but the Categoria adn Descrizione field are not > updated (empty) > Can you help me ? > Rather than use an unbound field I would suggest moving yout age calculation to a query. Anni: =DateDiff("yyyy",[Data_Nascita],Date())-IIf(Format([Data_Nascita],"mmdd")> Format(Date(),"mmdd"),1,0) Categoria and Descrizione are clearly related to age. Use a table with age as the key and relate them in the query. At that point you can stop writing code and never have to worry when Anni get to 73. You also have a query that can display all the informationacross many forms aND REPORTS.
From: Diego via AccessMonster.com on 4 May 2010 07:39 Hi ken the Data_Nascita, Categoria and Descrizione are in a table. Since this is a db for sport club, i need to save in the table the category (Categoria) and the description (Descrizione), related to the birth date (Data_Nascita). The date of birth (Data_Nascita) imposes the Categoria and Descrizione. I am trying to use age calculation with a unbounded field because i have no idea how to do this. My goal is to imposes the Categoria and Descrizione depending from Data_Nascita (that means age). I will try your suggestion and i will update asap. Thank you Diego KenSheridan wrote: >Diego: > >Why are you storing the Categoria and Descrizione values in fields in the >table? These will change as the age changes, so surely you want them to >reflect the current age. If you use unbound controls in the form (or in a >report or as a computed column in a query) you can compute the values >depending on the date of birth. > >Add the following two functions to a standard module in the database: > >Function GetCategoria(dtmData_Nascita As Date) As String > > Dim intAnni As Integer > > intAnni = DateDiff("yyyy", dtmData_Nascita, VBA.Date) - _ > IIf(Format(dtmData_Nascita, "mmdd") _ > > Format(VBA.Date, "mmdd"), 1, 0) > > If intAnni >= 12 And intAnni <= 14 Then > GetCategoria = "P_" & intAnni - 11 > Else > GetCategoria = "Not within age range" > End If > >End Function > >Function GetDesrcizione(dtmData_Nascita As Date) As String > > Dim intAnni As Integer > > intAnni = DateDiff("yyyy", dtmData_Nascita, VBA.Date) - _ > IIf(Format(dtmData_Nascita, "mmdd") _ > > Format(VBA.Date, "mmdd"), 1, 0) > > If intAnni >= 12 And intAnni <= 14 Then > GetDesrcizione = "Pulcini " & intAnni - 11 & "_A" > Else > GetDesrcizione = "Not within age range" > End If > >End Function > >Then in your form call the function's as the ControlSource properties of two >unbound controls: > >=GetCategoria([Data_Nascita]) > >=GetDesrcizione([Data_Nascita]) > >You can do the same in a report or use the expressions for a computed control >in a query. > >If there is some reason why you really do need to store the values in the >fields you can still use the functions in the AfterUpdate event of the >Data_Nascita control: > >Private Sub Data_Nascita_AfterUpdate() > > Me.Categoria = GetCategoria([Data_Nascita]) > Me.Descrizione = GetDescrizione ([Data_Nascita]) > >End Sub > >Ken Sheridan >Stafford, England > >>Just to clarify >>I have not problem to see the age calculation in the form Anni. >[quoted text clipped - 25 lines] >>>>>> Best regards >>>>>> Diego -- Message posted via http://www.accessmonster.com
From: Diego via AccessMonster.com on 4 May 2010 08:37
Hi Ken i tried In the first case with unbound control i have #name? In the second case with Me.Categoria = GetCategoria([Data_Nascita]) in the after update i have empty value. Let me know Diego KenSheridan wrote: >Diego: > >Why are you storing the Categoria and Descrizione values in fields in the >table? These will change as the age changes, so surely you want them to >reflect the current age. If you use unbound controls in the form (or in a >report or as a computed column in a query) you can compute the values >depending on the date of birth. > >Add the following two functions to a standard module in the database: > >Function GetCategoria(dtmData_Nascita As Date) As String > > Dim intAnni As Integer > > intAnni = DateDiff("yyyy", dtmData_Nascita, VBA.Date) - _ > IIf(Format(dtmData_Nascita, "mmdd") _ > > Format(VBA.Date, "mmdd"), 1, 0) > > If intAnni >= 12 And intAnni <= 14 Then > GetCategoria = "P_" & intAnni - 11 > Else > GetCategoria = "Not within age range" > End If > >End Function > >Function GetDesrcizione(dtmData_Nascita As Date) As String > > Dim intAnni As Integer > > intAnni = DateDiff("yyyy", dtmData_Nascita, VBA.Date) - _ > IIf(Format(dtmData_Nascita, "mmdd") _ > > Format(VBA.Date, "mmdd"), 1, 0) > > If intAnni >= 12 And intAnni <= 14 Then > GetDesrcizione = "Pulcini " & intAnni - 11 & "_A" > Else > GetDesrcizione = "Not within age range" > End If > >End Function > >Then in your form call the function's as the ControlSource properties of two >unbound controls: > >=GetCategoria([Data_Nascita]) > >=GetDesrcizione([Data_Nascita]) > >You can do the same in a report or use the expressions for a computed control >in a query. > >If there is some reason why you really do need to store the values in the >fields you can still use the functions in the AfterUpdate event of the >Data_Nascita control: > >Private Sub Data_Nascita_AfterUpdate() > > Me.Categoria = GetCategoria([Data_Nascita]) > Me.Descrizione = GetDescrizione ([Data_Nascita]) > >End Sub > >Ken Sheridan >Stafford, England > >>Just to clarify >>I have not problem to see the age calculation in the form Anni. >[quoted text clipped - 25 lines] >>>>>> Best regards >>>>>> Diego -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201005/1 |