I need to find the most recent date in a 'Date Tested' field in a table of records relating to test history, then add one year to arrive at a 'Test Due Date' and display this on the 'Testing' page (subform) of an 'Asset Details' form (whilst there are multiple past test dates, there can be only one 'Test Due Date').
As i am still at the relatively early learning stage with Access, I would appreciate help with this.
NOTE: Methods 2 & 3 assume dd/mm/yyyy format. Change the order if your locality uses a different format
4. Create your own functions:
Code:
Public Function AddCalendarYearToDateField(strText)
If Nz(strText, "") <> "" Then
If Format(strText, "dd/mm/") = "29/02/" Then 'modify to "28/02" to prevent leap year issues
AddCalendarYearToDateField = "28/02/" & Format(strText, "yyyy") + 1
Else
AddCalendarYearToDateField = Format(strText, "dd/mm/") & Format(strText, "yyyy") + 1
End If
End If
End Function
This function subtracts a year
Code:
Public Function SubtractCalendarYearFromDateField(strText)
If Nz(strText, "") <> "" Then
If Format(strText, "dd/mm/") = "29/02/" Then 'modify to "28/02" to prevent leap year issues
SubtractCalendarYearFromDateField = "28/02/" & Format(strText, "yyyy") - 1
Else
SubtractCalendarYearFromDateField = Format(strText, "dd/mm/") & Format(strText, "yyyy") - 1
End If
End If
NOTE: I strongly recommend you remove spaces from all field names. If you have spaces, you need to use [] e.g. [Date Tested]
