90 days and the first of the month
in [Queries]
|
Prev: Access 2007 Append Query � Issue with automatically inserted brack
Next: Access 2007 Append Query – Issue with automatically inserted b
From: Gaetan via AccessMonster.com on 18 May 2010 16:33 I'm trying to figure out hoe to calculate 90 days from hire and the first of the month Example: HiredDate Insurance activated 1/3/2010 4/1/2010 That is 90 days and the following 1st of the month Exception to the Rule: 1/1/2010 3/1/2010 If hired on the first its just HiredDate +90 All I came up with for my query is INSsurance: DateAdd("m",3,[DateHired]-Day([DateHired])+1) Any assistance wiuld greatly be appreciated Thanks G -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201005/1
From: John W. Vinson on 18 May 2010 21:21 On Tue, 18 May 2010 20:33:47 GMT, "Gaetan via AccessMonster.com" <u49408(a)uwe> wrote: >I'm trying to figure out hoe to calculate 90 days from hire and the first >of the month > >Example: > >HiredDate Insurance activated > >1/3/2010 4/1/2010 That is 90 days and the >following 1st of the month > >Exception to the Rule: > >1/1/2010 3/1/2010 If hired on the first its just >HiredDate +90 > > >All I came up with for my query is > >INSsurance: DateAdd("m",3,[DateHired]-Day([DateHired])+1) > >Any assistance wiuld greatly be appreciated > >Thanks > > >G Try DateSerial(Year([HiredDate]), Month([HiredDate])+IIF(Day([HiredDate]=1,3,4),1) This will get *three months* which is not necessarily equal to 90 days. -- John W. Vinson [MVP]
From: Gaetan via AccessMonster.com on 19 May 2010 10:28 Thanks for the reply I'm getting an error message on the following query. 90days from hire:DateSerial(Year([HiredDate]), Month([HiredDate])+IIF(Day( [HiredDate]=1,3,4),1) The error message is: The expression you have enterd has a function containing the wrong number of arguments. Any Ideas Gaetan John W. Vinson wrote: >>I'm trying to figure out hoe to calculate 90 days from hire and the first >>of the month >[quoted text clipped - 20 lines] >> >>G > >Try > >DateSerial(Year([HiredDate]), Month([HiredDate])+IIF(Day([HiredDate]=1,3,4),1) > >This will get *three months* which is not necessarily equal to 90 days. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201005/1
From: John Spencer on 19 May 2010 16:03 Missing a closing parenthesis in the Day(HiredDate) call DateSerial(Year([HiredDate]), Month([HiredDate])+ IIF(Day([HiredDate])=1,3,4),1) John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Gaetan via AccessMonster.com wrote: > Thanks for the reply > > I'm getting an error message on the following query. > > 90days from hire:DateSerial(Year([HiredDate]), Month([HiredDate])+IIF(Day( > [HiredDate]=1,3,4),1) > > The error message is: The expression you have enterd has a function > containing the wrong > number of arguments. > > Any Ideas > > Gaetan > > John W. Vinson wrote: >>> I'm trying to figure out hoe to calculate 90 days from hire and the first >>> of the month >> [quoted text clipped - 20 lines] >>> G >> Try >> >> DateSerial(Year([HiredDate]), Month([HiredDate])+IIF(Day([HiredDate]=1,3,4),1) >> >> This will get *three months* which is not necessarily equal to 90 days. >
From: Gaetan via AccessMonster.com on 20 May 2010 12:04
John Thanks That worked Great!! John Spencer wrote: >Missing a closing parenthesis in the Day(HiredDate) call > >DateSerial(Year([HiredDate]), Month([HiredDate])+ >IIF(Day([HiredDate])=1,3,4),1) > >John Spencer >Access MVP 2002-2005, 2007-2010 >The Hilltop Institute >University of Maryland Baltimore County > >> Thanks for the reply >> >[quoted text clipped - 20 lines] >>> >>> This will get *three months* which is not necessarily equal to 90 days. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201005/1 |