From: Sassy on 4 Jun 2010 13:52 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
From: KenSheridan via AccessMonster.com on 4 Jun 2010 18:44 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/Forums.aspx/access-gettingstarted/201006/1
|
Pages: 1 Prev: where can I find a 2010 attendance tracker Next: Does SQL Run Behind Queries? |