If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Date
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 -- Thank You Sassy |
#2
|
|||
|
|||
Date
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 Sassy wrote: 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/201006/1 |
Thread Tools | |
Display Modes | |
|
|