Use calculate age to write other fields automatically
in [Forms]
|
Prev: cancel form unload
Next: MDE in A2003, A2007
From: KenSheridan via AccessMonster.com on 4 May 2010 12:55 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 Diego wrote: >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] >>>>>>> Best regards >>>>>>> Diego -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201005/1
From: Diego via AccessMonster.com on 5 May 2010 05:02 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 KenSheridan wrote: >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] >>>>>>>> Best regards >>>>>>>> Diego -- Message posted via http://www.accessmonster.com
From: Diego via AccessMonster.com on 5 May 2010 05:22 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 KenSheridan wrote: >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] >>>>>>>> Best regards >>>>>>>> Diego -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201005/1
From: Diego via AccessMonster.com on 5 May 2010 05:38 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 Diego wrote: >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] >>>>>>>>> Best regards >>>>>>>>> Diego -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201005/1
From: Diego via AccessMonster.com on 5 May 2010 05:45
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 Diego wrote: >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] >>>>>>>>>> Best regards >>>>>>>>>> Diego -- Message posted via http://www.accessmonster.com |