Date Criteria

[jschneider998]

I am creating a HR database. I am trying to create a query to pull up employees that are eligible for benefits. They are eligable the first of the month after their 6 month anniversary. Is there a way in a query criteria to generate 6 months plus however many days until the first of the next month????

 

[pcEars]

Have a look at this

 

[namliam]

6MonthBenifit: dateadd("M",7,format(#15-jan-2003#-1,"MM-YYYY"))

change #15-jan-2003# to your Hiredate from your table...

Above does this:

format(#15-jan-2003#-1,"MM-YYYY") = 01-2003
(jan 2003, or in a date format that "Rounds down" to 01 jan 2003)
Then the dateadd adds 7 months to get to the 01 of the month after the 6 month anniversary (this case Ann 15-jul, paydate: 01-aug)

However if the -1 was not added 01-jan would also become 01-aug whereas that is on 01-jul ( I Think, depends on your logic tho ) Thus taking the one off 02-jan becomes 01-jan thus Benifit pay: 01- aug (ann: 02-jul)

01-jan becomes 31-dec 2002, Benifit pay & Ann: 01-jul

Regards

 

[raskew]

From the debug window:

hiredate = #15-Jan-2003#
benefitdate = dateserial(year(hiredate), month(hiredate)+7, 1)
? format(benefitdate, "medium date")
01-Aug-03