Date query with a flag

[grades]

Hey,
I'm not sure this is an Access specific problem but more programatical than anything. But maybe someone here has an idea to help me out.

I am creating an employee database, a large part of which is "letters" meaning employees get a warning letter if they are late etc. If they need a letter, my queries search 6 months in the past to see if they've already gotten a letter and gives them the next one in line (verbal, written, final, termination).

This is all well and good, but ive recently discovered that employees have a 3 month probation period, after which any letters needed will start out at the verbal letter again.

What i'm looking for is some ideas on how to go about this with relative ease. I'd prefer to make it work with my current set-up and not have to change all my reports and queries as there are scads of them.

An example query might go something like:
Return past letters Between [forms]![frmmain]![s1] And Date()
(s1 being a date 6 months ago)
How can i incorporate something like DateSerial along with my hireDate field to find out if they are past the probation period (and not include those letters), but to include those letters if they still are in the probation period?

Sorry if ive explained poorly, ive been working on this all weekend.
Thanks,

 

[rammudali]

hi, may be you can found with a datediff function whether he is more than 90 days from date of joining and total number of days from date of joining and filter as per your requirement

 

[grades]

Datediff is more or less what i had been trying, but just by math.
If i search for letters within 6 months but not Between hireDate and hiredate+90, then it works except that if the employee is STILL in probation, it returns no results.
Still stumped by this..

 

[rammudali]

hi

doj is date of joining, date is current date. in the query try datediff("d",[doj],[date]) and in the criteria <=270 will give you records of probation period + 6 months. further if you want to filter employees who have cleared probation with >=90 and <= 270 etc. and under probation with <= 90

enjoy!!!!!!!!!!!!!!!