Complex Query

[losthome]

Hi There,

I am hoping someone can help me with my problem that I am having. I have a query that is using the fucntion DateAdd. What I am trying to do is have the query pick through the list of employees who's 90 probate period is up and display them. The problem that I am running into is that my client needs to have a two week period before the actually 90 days is up. So far I can only pick an exact day. He needs to have the 2 week period just incase he doesn't run the program on day.

For example,
Jennifer's start date is 01/01/02. 90 days probate is up on 01/04/02. My client needs to see that person show up on the list if he runs the query between 18/03/02 through 01/04/02

Field: nintydayreview: DateAdd("d",83,[EmployeeStartDate])
Criteria: [forms]![frm-90dayreviewtime]![meetingdate]

Thank you
Jennifer

 

[thouston]

Have you thought of doing it with an Iif statment?

You could make a field like this:

Field: IIf([EmployeeStartDate]+90>=Now()-14 And [EmployeeStartDate]+90<Now(),"Time for meeting",Null)

This will print "Time for meeting" if the date falls between the employee start date and the 14 day period before 90 days is up. You could replace the text value "Time for meeting" with an actual date or another field from the database if you wanted.

To make it more foolproof (say your client goes away for more than 2 weeks and forgets to run the query), you could get it to catch any overdue cases like this:

Field: IIf([EmployeeStartDate]+90>=Now()-14 And [EmployeeStartDate]+90<Now(),"Time for meeting",(IIf([EmployeeStartDate]+90<Now(),"MEETING OVERDUE!",Null)))

I haven't checked whether the < should be <= etc but you get the general idea.

Good luck!

 

[Rich]

Replace Now() with Date()