Date

[Sassy]

I have a table with a start date column. I have created a query where I
include the start date from my table, I have created an expression to add 24
months to the start date but what I would like to also have is either the
specific date or the closest month of May.
for example Start date is July 1, 2008 if I add 24 months it will show July
1, 2010
What I would like to see is May 2010 as this the month that we identify
individuals that need to start a secondary process.

Very new to access and not that great with formulas

[KenSheridan via AccessMonster.com]

By 'closest' do you mean the May preceding the date 24 months from the start
date? If so the following expression should do it:

Format(DateAdd("m",IIf(Month([StartDate]) < 5,17 - Month([StartDate]),29 -
Month([StartDate])),[StartDate]), "mmmm yyyy")

If by 'closest' you mean the nearest May before or after the date 24 months
from the start date then you'd need something a little more complex. You'd
probably be better wrapping the code in a little function for this:

Public Function StartSecondary(dtmStart As Date) As String

Dim dtmTwoYears As Date

' get date two years ahead of start date
dtmTwoYears = DateAdd("yyyy", 2, dtmStart)

' if month between May and November get previous May
' else get next may
If Month(dtmTwoYears) >= 5 _
And Month(dtmTwoYears) <= 10 Then

StartSecondary = Format(DateAdd("m", IIf(Month(dtmStart) < 5, 17 - _
Month(dtmStart), 29 - Month(dtmStart)), dtmStart), "mmmm yyyy")

Else

StartSecondary = Format(DateAdd("m", IIf(Month(dtmStart) < 5, 29 - _
Month(dtmStart), 41 - Month(dtmStart)), dtmStart), "mmmm yyyy")

End If

End Function

For this I've assumed that for a date between May and November the 'closest'
May would be the previous one, after November, the next one. Just paste the
function into a standard module. Save the module under a different name from
the function, e.g. basDateStuff. The function can then be called in a
computed column query, passing the StartDate value into it:

StartSecondary([StartDate])

Ken Sheridan
Stafford, England

Post by Sassy
I have a table with a start date column. I have created a query where I
include the start date from my table, I have created an expression to add 24
months to the start date but what I would like to also have is either the
specific date or the closest month of May.
for example Start date is July 1, 2008 if I add 24 months it will show July
1, 2010
What I would like to see is May 2010 as this the month that we identify
individuals that need to start a secondary process.
Very new to access and not that great with formulas