Complex If Statement

[losthome]

Hi There,

I am still having major troblems trying to get my query and form to do what I want. I have had some suggestions and I have tried them without success. I have tried the following and It doesn't do anything.

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

I have a datebase that has employee's and their start dates. I need to have a query that filters the people when the employee's 90 days are up. I have a form that has the current date and it is linked to a query of the employees. It needs to bring up the employees in a two week period. For example, John's start date is 02/03/02. If the supervisor checks the database any days from 17/05/2002 through 31/05/2002 and it brings up his name. Then the supervisor knows it is time for a review. I can get it to work using DateAdd("d",90,[EmployeeStartDate]), but the problem is it is limited to only 90 days exact. I have been trying to get it to do 76 to 90 days, but the query complains that it is to complex.

Any suggestions would greatly be Appreciated

Thank you,

Jennifer

 

[Cosmos75]

Try this

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


Edit: added all the color and bold formating!

(Yes, I had nothing better to do and it helps to someone to digest the formula better.)

Did this AND(([EmployeeStartDate]+90)>=(Date()-14),([EmployeeStartDate]+90)<Date()) to show that this is all part of the same AND(Criteria1, Criteria2)

 

[losthome]

Tried

Hi there,

Thank you for your suggestion, but it says The expression you entered contains invalid syntax.

I say ok and it highlights AND.

Any clues of why?

Thank you again for your help.

Jennifer

 

[Cosmos75]

Try this

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

 

[Cosmos75]

AND function not like in EXCEL?

I guess AND(Criteria1, Criteria2) only works for EXCEL? So we BOTH learned something..

 

[losthome]

Thank you so much. it works perfect!

Jennifer

 

[Cosmos75]

Jennifer,

You are very welcome!

Always glad to help (although I more often than not may not be of much help and even more likely to be asking for help!)


Edit: I may just add that last sentence in my signature!