Most recent date plus one year

PeteB · Jan 22, 2018

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.

isladogs · Jan 22, 2018

There are many ways of doing this in Access
Here are a few to choose from - IMO number 3 is probably the simplest

dteDate represents a date e.g. dteDate = #27/01/2016# or #1/27/2016#

1. Add 365 days to date - obvious issue for leap years

Code:

dteDate+365

2. Use Access DatePart functions

Code:

DatePart("d", dteDate) & "/" & DatePart("m",dteDate) & "/" & (DatePart("yyyy",dteDate)+1)

3. Use Access Day/Month/Year functions

Code:

Day(dteDate) & "/" & Month(dteDate) & "/" & (Year(dteDate)+1)

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]

arnelgp · Jan 22, 2018

Add an unbound textbox on Testing subform. Put this as its recordsource:

=DateAdd("yyyy", 1, Dmax("[testing date]", "[tablename]") )

sonic8 · Jan 22, 2018

ridders said:
There are many ways of doing this in Access
Here are a few to choose from - IMO number 3 is probably the simplest

All your suggestions, except the first one, may fail depending on the Regional Date/Time settings on the executing computer.

I would go for:
DateAdd("yyyy",1,dteDate)

isladogs · Jan 22, 2018

DateAdd("yyyy",1,dteDate)

Now why did I leave out the simplest method of all?
Agreed - use that one

Mark_ · Jan 22, 2018

ridders said:
Now why did I leave out the simplest method of all?
Agreed - use that one

For myself, I would normally blame a severe lack of caffeine...

Most recent date plus one year

PeteB

Registered User.

isladogs

MVP / VIP

arnelgp

..forever waiting... waiting for jellybean!

sonic8

AWF VIP

isladogs

MVP / VIP

Mark_

Longboard on the internet

Similar threads

Users who are viewing this thread