From: Diego via AccessMonster.com on
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
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
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
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
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

First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4 5
Prev: cancel form unload
Next: MDE in A2003, A2007