Use calculate age to write other fields automatically

[Diego via AccessMonster.com]

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 ?

Best regards
Diego

 

[Al Campagna]

Diego,
Since those values depend on the Data_Nascita, you need to use the
AfterUpdate event of Data_Nascita to update those fields.
Also, you can't use the "and" to update the Descrizione.

If Me.Anni = 12 Then
Me.Categoria = "P_1" Me.Descrizione = "Pulcini 1_A"
ElseIf Me.Anni = 13 Then
Me.Categoria = "P_2" Me.Descrizione = "Pulcini 2_A"
ElseIf Me.Anni = 14 Then
Me.Categoria = "P_3" Me.Descrizione = "Pulcini 3_A"
End If

But... you'll probably also need to have that same code on the Form's
OnCurrent event, so... as a person's age increases, the Categoria and
Descrizione
will adjust when the user views/browses that record.
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

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 ?

Best regards
Diego

 

[Diego via AccessMonster.com]

It does not works
the problem seems to be in the age calculation

Anni is a unbounded field, that gives a good age value, but when i use in the
expression it does not works.
Diego

Diego,
Since those values depend on the Data_Nascita, you need to use the
AfterUpdate event of Data_Nascita to update those fields.
Also, you can't use the "and" to update the Descrizione.

If Me.Anni = 12 Then
Me.Categoria = "P_1" Me.Descrizione = "Pulcini 1_A"
ElseIf Me.Anni = 13 Then
Me.Categoria = "P_2" Me.Descrizione = "Pulcini 2_A"
ElseIf Me.Anni = 14 Then
Me.Categoria = "P_3" Me.Descrizione = "Pulcini 3_A"
End If

But... you'll probably also need to have that same code on the Form's
OnCurrent event, so... as a person's age increases, the Categoria and
Descrizione
will adjust when the user views/browses that record.

Hi All
i have a date field called Data_Nascita and and unbound field to calculate

[quoted text clipped - 25 lines]

Best regards
Diego

 

[Al Campagna]

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...
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

It does not works
the problem seems to be in the age calculation

Anni is a unbounded field, that gives a good age value, but when i use in
the
expression it does not works.
Diego

Diego,
Since those values depend on the Data_Nascita, you need to use the
AfterUpdate event of Data_Nascita to update those fields.
Also, you can't use the "and" to update the Descrizione.

If Me.Anni = 12 Then
Me.Categoria = "P_1" Me.Descrizione = "Pulcini 1_A"
ElseIf Me.Anni = 13 Then
Me.Categoria = "P_2" Me.Descrizione = "Pulcini 2_A"
ElseIf Me.Anni = 14 Then
Me.Categoria = "P_3" Me.Descrizione = "Pulcini 3_A"
End If

But... you'll probably also need to have that same code on the Form's
OnCurrent event, so... as a person's age increases, the Categoria and
Descrizione
will adjust when the user views/browses that record.

Hi All
i have a date field called Data_Nascita and and unbound field to
calculate

[quoted text clipped - 25 lines]

Best regards
Diego

 

[Linq Adams via AccessMonster.com]

Actually, you can use the expression as the Control Source of the unbound
textbox and it works fine, and will update automatically as the person ages.

Al's suggestion of using the Data_Nascita_AfterUpdate event as well as the
Form_Current event would, of course, also work. It's simply a matter of style.


Where exactly are you trying to use the expression?

 

[Diego via AccessMonster.com]

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
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]

 

[KenSheridan via AccessMonster.com]

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.
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]

 

[Mike Painter]

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.

 

[Diego via AccessMonster.com]

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

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]

 

[Diego via AccessMonster.com]

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

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]

 

[KenSheridan via AccessMonster.com]

Diego:

The #name error is probably because I'd put the 'c' and 'r' the wrong way
round in the function name. It should have been:

Function GetDescrizione(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
GetDescrizione = "Pulcini " & intAnni - 11 & "_A"
Else
GetDescrizione = "Not within age range"
End If

End Function

Apart from that I see no reason why both should not work. Data_Nascita
should be a bound control containing the date of birth as a date/time data
type, and the code should be in its AfterUpdate event procedure. I still
don't see any reason why the Categoria and Descrizione values need be stored
in the table, however, as they can be computed from the Data_Nascita value at
any time, so are redundant.

While the values can be obtained with functions in this way, its not really a
good way of doing it as it requires data to be represented in the code. In a
relational database data should only be stored in tables. A better way would
be to have another table, Anni_Categoria say, with columns Anni, Descrizione
and Categoria, with the following rows:

12 Pulcini 1_A P_1
13 Pulcini 2_A P_2
14 Pulcini 3_A P_3

You can then look up the values from that table in the Data_Nascita control's
AfterUpdate event procedure:

Dim strCriteria As String
Dim dtmData_Nascita As Date

dtmData_Nascita = Me.Data_Nascita

strCriteria = "Anni = " & _
DateDiff("yyyy", dtmData_Nascita, VBA.Date) - _
IIf(Format(dtmData_Nascita, "mmdd") _

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

Me.Descrizione = DLookup("Descrizione","Anni_Descrizione",strCriteria)
Me.Categoria = DLookup("Categoria","Anni_Descrizione",strCriteria)

By having the data in the Anni_Categoria table it can be updated at any time
simply by editing records in the table, so if further categories are added or
the basis for determining the categories changes the is no need to amend any
code.

Ken Sheridan
Stafford, England

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

Diego:

[quoted text clipped - 67 lines]

 

[Diego via AccessMonster.com]

I saw the issue and i corrected it, but i have tested only the category .
Related to your suggestion you are right since yesterday i had from club the
last version of all categories that they have.
It is a long list, so the better solution is to use your suggestion with a
table.
I try it and i will update asap.

BR
Diego

Diego:

The #name error is probably because I'd put the 'c' and 'r' the wrong way
round in the function name. It should have been:

Function GetDescrizione(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
GetDescrizione = "Pulcini " & intAnni - 11 & "_A"
Else
GetDescrizione = "Not within age range"
End If

End Function

Apart from that I see no reason why both should not work. Data_Nascita
should be a bound control containing the date of birth as a date/time data
type, and the code should be in its AfterUpdate event procedure. I still
don't see any reason why the Categoria and Descrizione values need be stored
in the table, however, as they can be computed from the Data_Nascita value at
any time, so are redundant.

While the values can be obtained with functions in this way, its not really a
good way of doing it as it requires data to be represented in the code. In a
relational database data should only be stored in tables. A better way would
be to have another table, Anni_Categoria say, with columns Anni, Descrizione
and Categoria, with the following rows:

12 Pulcini 1_A P_1
13 Pulcini 2_A P_2
14 Pulcini 3_A P_3

You can then look up the values from that table in the Data_Nascita control's
AfterUpdate event procedure:

Dim strCriteria As String
Dim dtmData_Nascita As Date

dtmData_Nascita = Me.Data_Nascita

strCriteria = "Anni = " & _
DateDiff("yyyy", dtmData_Nascita, VBA.Date) - _
IIf(Format(dtmData_Nascita, "mmdd") _

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

Me.Descrizione = DLookup("Descrizione","Anni_Descrizione",strCriteria)
Me.Categoria = DLookup("Categoria","Anni_Descrizione",strCriteria)

By having the data in the Anni_Categoria table it can be updated at any time
simply by editing records in the table, so if further categories are added or
the basis for determining the categories changes the is no need to amend any
code.

Ken Sheridan
Stafford, England

Hi Ken
i tried

[quoted text clipped - 12 lines]

 

[Diego via AccessMonster.com]

Hi Ken
also your suggestion does not works. The Categoria and Descrizione are empty.
It seem that the calculation of strCriteria does not works.
My date in the table has medium format (21-May-10) . This can be a problem ?
I use this format to avoid difference from Italy and USA date format.
Sure this method is better.
Let me know
Diego

Diego:

The #name error is probably because I'd put the 'c' and 'r' the wrong way
round in the function name. It should have been:

Function GetDescrizione(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
GetDescrizione = "Pulcini " & intAnni - 11 & "_A"
Else
GetDescrizione = "Not within age range"
End If

End Function

Apart from that I see no reason why both should not work. Data_Nascita
should be a bound control containing the date of birth as a date/time data
type, and the code should be in its AfterUpdate event procedure. I still
don't see any reason why the Categoria and Descrizione values need be stored
in the table, however, as they can be computed from the Data_Nascita value at
any time, so are redundant.

While the values can be obtained with functions in this way, its not really a
good way of doing it as it requires data to be represented in the code. In a
relational database data should only be stored in tables. A better way would
be to have another table, Anni_Categoria say, with columns Anni, Descrizione
and Categoria, with the following rows:

12 Pulcini 1_A P_1
13 Pulcini 2_A P_2
14 Pulcini 3_A P_3

You can then look up the values from that table in the Data_Nascita control's
AfterUpdate event procedure:

Dim strCriteria As String
Dim dtmData_Nascita As Date

dtmData_Nascita = Me.Data_Nascita

strCriteria = "Anni = " & _
DateDiff("yyyy", dtmData_Nascita, VBA.Date) - _
IIf(Format(dtmData_Nascita, "mmdd") _

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

Me.Descrizione = DLookup("Descrizione","Anni_Descrizione",strCriteria)
Me.Categoria = DLookup("Categoria","Anni_Descrizione",strCriteria)

By having the data in the Anni_Categoria table it can be updated at any time
simply by editing records in the table, so if further categories are added or
the basis for determining the categories changes the is no need to amend any
code.

Ken Sheridan
Stafford, England

Hi Ken
i tried

[quoted text clipped - 12 lines]

 

[Diego via AccessMonster.com]

Hi Ken
the problem is related to format date.

I have a code that permit me to select data from a form

On dbl Click
Me!Data_Nascita = InputCalendario(Nz(Me!Data_Nascita, 0), "Data_Nascita")
take the data value from a Input Calendar
This works fine, but i chose the date format medium (21-May-10) and would
like to use this.

Is it possible?
Let me know
Diego

Hi Ken
also your suggestion does not works. The Categoria and Descrizione are empty.
It seem that the calculation of strCriteria does not works.
My date in the table has medium format (21-May-10) . This can be a problem ?
I use this format to avoid difference from Italy and USA date format.
Sure this method is better.
Let me know
Diego

Diego:

[quoted text clipped - 63 lines]

 

[Diego via AccessMonster.com]

Hi Ken again
the problem is not in the format date, but how the date is selected.
If i write by hand the date it works fine, but if i select from InputCalendar
it does not works.
It is like if i write nothing in the date . Strange, because the date in the
table is write correctly.

No idea how to solve this issue.
Below the InputCalendario Function

Sorry by that
Let me know
Diego




Public Function InputCalendario(Data As String, Titolo)

'*********************************************************************************************
'* Funzione che visualizza il calendario e restituisce la data scelta
'* Argomenti ricevuti: - Data; se = 0, viene proposta la data del giorno
'* - Titolo della maschera 'Calendario'
'* Valore restituito: - Data scelta dall'utente; se è stato premuto il tasto
di uscita:
'* - Data ricevuta in input, se > 0
'* - Valore Null, se = 0
'* Non viene reso 0, perchè Access lo interpreta come
30/12/1899
'* (non 31/12/1899 perché per Microsoft il 1900 era
bisestile)
'* - La posizione in cui viene visualizzato il calendario viene calcolata in
base
'* alla posizione del mouse
'*********************************************************************************************

Dim stDocName As String, frm As Form
Dim Posizione As POINTAPI
Dim PuntoX As Integer, PuntoY As Integer
Dim SpostamentoX As Integer, SpostamentoY As Integer, Larghezza As
Integer, Altezza As Integer

' Calcola i valori per la gestione dello schermo
ValoriSchermo

' Determina la posizione del mouse
' La funzione GetCursorPos restituisce in una variabile di tipo definito
dall’utente denominata POINTAPI le coordinate x,y relative all’angolo
superiore a sinistra dello schermo.
GetCursorPos Posizione

'Ricavo la posizione x e y
PuntoX = Posizione.X 'coordinata del punto x
PuntoY = Posizione.Y 'coordinata del punto y

' Calcolo la posizione della maschera
' - Posizione X = Pos.-X- del mouse + SpostamentoX
' se, sommata alla larghezza della maschera, supera il lato destro
dello schermo:
' Posizione X = Pos.-X- del mouse - SpostamentoX - larghezza maschera
' Il calcolo per -Y- è analogo
' Dimensioni maschera: dimensioni in cm * 567 (1 cm = 567 Twips)

Larghezza = 4 * 567 ' Larghezza maschera calendario
Altezza = 6 * 567
SpostamentoX = 800
SpostamentoY = 0

PosDx = (PuntoX) * TwipsPerPixelX
PosDx = IIf(((PosDx + SpostamentoX + Larghezza) > MaxTwipsX), (PosDx -
SpostamentoX - Larghezza), (PosDx + SpostamentoX))
PosVr = (PuntoY) * TwipsPerPixelY
PosVr = IIf(((PosVr + SpostamentoY + Altezza) > MaxTwipsY), (PosVr -
SpostamentoY - Altezza), (PosVr + SpostamentoY))

TitCal = Titolo
stDocName = "Calendario"
DataIn = Data

'Apri in modale la maschera
DoCmd.OpenForm stDocName, , , , , acDialog

InputCalendario = DataOut

End Function

Hi Ken
the problem is related to format date.

I have a code that permit me to select data from a form

On dbl Click
Me!Data_Nascita = InputCalendario(Nz(Me!Data_Nascita, 0), "Data_Nascita")
take the data value from a Input Calendar
This works fine, but i chose the date format medium (21-May-10) and would
like to use this.

Is it possible?
Let me know
Diego

Hi Ken
also your suggestion does not works. The Categoria and Descrizione are empty.

[quoted text clipped - 10 lines]

 

[KenSheridan via AccessMonster.com]

Diego:

Apologies for the delay in replying. I've been visiting my new granddaughter
who was born this morning. Una bambina bella!

I think the problem is that when you assign a value to a control with code
the control's AfterUpdate event does not in fact execute. It only does so if
the user enters data manually. This explains why it works when you type the
date in, but not when its assigned by the InputCalendario function.

What you can try, however, is calling the functions to insert the values into
the controls from within the InputCalendario function by adding lines at the
end like this:

InputCalendario = DataOut

' add these 3 lines
On Error Resume Next
Forms!YourFormname!Categoria = GetCategoria(DataOut)
Forms!YourFormname!Descrizione = GetDescrizione (DataOut)

End Function

The On Error Resume Next line is there in case the function is called while
the form which contains the Categoria and Descrizione controls is not open,
in which case the error will be ignored.

Ken Sheridan
Stafford, England

Hi Ken again
the problem is not in the format date, but how the date is selected.
If i write by hand the date it works fine, but if i select from InputCalendar
it does not works.
It is like if i write nothing in the date . Strange, because the date in the
table is write correctly.

No idea how to solve this issue.
Below the InputCalendario Function

Sorry by that
Let me know
Diego

Public Function InputCalendario(Data As String, Titolo)

'*********************************************************************************************
'* Funzione che visualizza il calendario e restituisce la data scelta
'* Argomenti ricevuti: - Data; se = 0, viene proposta la data del giorno
'* - Titolo della maschera 'Calendario'
'* Valore restituito: - Data scelta dall'utente; se è stato premuto il tasto
di uscita:
'* - Data ricevuta in input, se > 0
'* - Valore Null, se = 0
'* Non viene reso 0, perchè Access lo interpreta come
30/12/1899
'* (non 31/12/1899 perché per Microsoft il 1900 era
bisestile)
'* - La posizione in cui viene visualizzato il calendario viene calcolata in
base
'* alla posizione del mouse
'*********************************************************************************************

Dim stDocName As String, frm As Form
Dim Posizione As POINTAPI
Dim PuntoX As Integer, PuntoY As Integer
Dim SpostamentoX As Integer, SpostamentoY As Integer, Larghezza As
Integer, Altezza As Integer

' Calcola i valori per la gestione dello schermo
ValoriSchermo

' Determina la posizione del mouse
' La funzione GetCursorPos restituisce in una variabile di tipo definito
dall’utente denominata POINTAPI le coordinate x,y relative all’angolo
superiore a sinistra dello schermo.
GetCursorPos Posizione

'Ricavo la posizione x e y
PuntoX = Posizione.X 'coordinata del punto x
PuntoY = Posizione.Y 'coordinata del punto y

' Calcolo la posizione della maschera
' - Posizione X = Pos.-X- del mouse + SpostamentoX
' se, sommata alla larghezza della maschera, supera il lato destro
dello schermo:
' Posizione X = Pos.-X- del mouse - SpostamentoX - larghezza maschera
' Il calcolo per -Y- è analogo
' Dimensioni maschera: dimensioni in cm * 567 (1 cm = 567 Twips)

Larghezza = 4 * 567 ' Larghezza maschera calendario
Altezza = 6 * 567
SpostamentoX = 800
SpostamentoY = 0

PosDx = (PuntoX) * TwipsPerPixelX
PosDx = IIf(((PosDx + SpostamentoX + Larghezza) > MaxTwipsX), (PosDx -
SpostamentoX - Larghezza), (PosDx + SpostamentoX))
PosVr = (PuntoY) * TwipsPerPixelY
PosVr = IIf(((PosVr + SpostamentoY + Altezza) > MaxTwipsY), (PosVr -
SpostamentoY - Altezza), (PosVr + SpostamentoY))

TitCal = Titolo
stDocName = "Calendario"
DataIn = Data

'Apri in modale la maschera
DoCmd.OpenForm stDocName, , , , , acDialog

InputCalendario = DataOut

End Function

Hi Ken
the problem is related to format date.

[quoted text clipped - 16 lines]

 

[Mike Painter]

Hi Ken
also your suggestion does not works. The Categoria and Descrizione
are empty. It seem that the calculation of strCriteria does not works.
My date in the table has medium format (21-May-10) . This can be a
problem ? I use this format to avoid difference from Italy and USA
date format.
Sure this method is better.
Let me know
Diego

To repeat what I said a few posts ago.
THIS IS A RELATIONAL DATABASE.

Place the age calculation in a query, relate the Categoria and Descrizione
table in that query.
Use the query for all forms and reports.
No "lookup" is needed.

 

[Diego via AccessMonster.com]

Great !!!
It is wonderful !!!!
Welcome at your new granddaughter and greetings from Italy to you and your
family.

Thank you for all your suggestions
Ciao from Italy
Diego

Thank you for

Diego:

Apologies for the delay in replying. I've been visiting my new granddaughter
who was born this morning. Una bambina bella!

I think the problem is that when you assign a value to a control with code
the control's AfterUpdate event does not in fact execute. It only does so if
the user enters data manually. This explains why it works when you type the
date in, but not when its assigned by the InputCalendario function.

What you can try, however, is calling the functions to insert the values into
the controls from within the InputCalendario function by adding lines at the
end like this:

InputCalendario = DataOut

' add these 3 lines
On Error Resume Next
Forms!YourFormname!Categoria = GetCategoria(DataOut)
Forms!YourFormname!Descrizione = GetDescrizione (DataOut)

End Function

The On Error Resume Next line is there in case the function is called while
the form which contains the Categoria and Descrizione controls is not open,
in which case the error will be ignored.

Ken Sheridan
Stafford, England

Hi Ken again
the problem is not in the format date, but how the date is selected.

[quoted text clipped - 84 lines]

 

[Diego via AccessMonster.com]

Hi Ken
since your suggestion works fine i have a new question
How to do the same thing in a query ?

I refer to your last suggestion

------------------------------------------------------------------------------
---------------------------

strCriteria = "Anni = " & _
DateDiff("yyyy", dtmData_Nascita, VBA.Date) - _
IIf(Format(dtmData_Nascita, "mmdd") _

Me.Descrizione = DLookup("Descrizione","Anni_Descrizione",strCriteria)
Me.Categoria = DLookup("Categoria","Anni_Descrizione",strCriteria)

------------------------------------------------------------------------------
--------------------------

Ciao
Diego

Diego:

The #name error is probably because I'd put the 'c' and 'r' the wrong way
round in the function name. It should have been:

Function GetDescrizione(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
GetDescrizione = "Pulcini " & intAnni - 11 & "_A"
Else
GetDescrizione = "Not within age range"
End If

End Function

Apart from that I see no reason why both should not work. Data_Nascita
should be a bound control containing the date of birth as a date/time data
type, and the code should be in its AfterUpdate event procedure. I still
don't see any reason why the Categoria and Descrizione values need be stored
in the table, however, as they can be computed from the Data_Nascita value at
any time, so are redundant.

While the values can be obtained with functions in this way, its not really a
good way of doing it as it requires data to be represented in the code. In a
relational database data should only be stored in tables. A better way would
be to have another table, Anni_Categoria say, with columns Anni, Descrizione
and Categoria, with the following rows:

12 Pulcini 1_A P_1
13 Pulcini 2_A P_2
14 Pulcini 3_A P_3

You can then look up the values from that table in the Data_Nascita control's
AfterUpdate event procedure:

Dim strCriteria As String
Dim dtmData_Nascita As Date

dtmData_Nascita = Me.Data_Nascita

strCriteria = "Anni = " & _
DateDiff("yyyy", dtmData_Nascita, VBA.Date) - _
IIf(Format(dtmData_Nascita, "mmdd") _

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

Me.Descrizione = DLookup("Descrizione","Anni_Descrizione",strCriteria)
Me.Categoria = DLookup("Categoria","Anni_Descrizione",strCriteria)

By having the data in the Anni_Categoria table it can be updated at any time
simply by editing records in the table, so if further categories are added or
the basis for determining the categories changes the is no need to amend any
code.

Ken Sheridan
Stafford, England

Hi Ken
i tried

[quoted text clipped - 12 lines]

 

[KenSheridan via AccessMonster.com]

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
How to do the same thing in a query ?

I refer to your last suggestion

------------------------------------------------------------------------------
---------------------------

strCriteria = "Anni = " & _
DateDiff("yyyy", dtmData_Nascita, VBA.Date) - _

[quoted text clipped - 3 lines]

Me.Descrizione = DLookup("Descrizione","Anni_Descrizione",strCriteria)
Me.Categoria = DLookup("Categoria","Anni_Descrizione",strCriteria)
------------------------------------------------------------------------------
--------------------------

Ciao
Diego

Diego:

[quoted text clipped - 63 lines]