Problems with expression

[king07]

Okay--

havent gotten an answer yet so will post again. Here is essentially what i am trying to do-- what i have done up to this point.

I have a field which is listed as STATUS-- i want to be able to do a calculation for the one of the three following options to come up--Overdue, Completed,Upcoming

The other fields to which i am working from are Last Review Date, and Next discussion due. Essentially we have employee reviews once a year (hence, last review date) and need the date for the following year(hence next discussion due) i already have the dateadd to add a year to the next discussion due so that field works fine.I should also mention that our "years" (being fiscal) go from Oct 31-Nov 1

For the status field i have the following expression and am completely, utterly stumped at how to add in the third option.

Here is what i have so far.....

Status: IIf([Next Discussion Due]<Date(),"OverDue",IIf([Next Discussion Due]>Date(),"UpComing",""))

now i am stumped at how i can have the completed show up-- essentially if they have their review done in the fiscal year then it should be completed-- at this time the overdue's show up properly and everything else shows up as Upcoming-- i have also tried using the switch function instead which was no help at all-- is there a way to add in a datediff expression in this IIf to get the Completed to show up??

Again thanks for the help-- i am by far no genius at this stuff.....

 

[FoFa]

Well your problem is detecting the [Next Discussion Due] within the current fiscal year or in the next fiscal year (which by default means you know which fiscal year you are in).
So basically given we checked for overdue already, IF [Next Discussion Due] is in the current fiscal year then it is upcoming. IF [Next Discussion Due] is in the next fiscal year, then it is complete.
OR I misunderstand the question

 

[king07]

That is exactly what i am asking--

thanks...

just need to kink out the formula....

 

[king07]

Well your problem is detecting the [Next Discussion Due] within the current fiscal year or in the next fiscal year (which by default means you know which fiscal year you are in).
So basically given we checked for overdue already, IF [Next Discussion Due] is in the current fiscal year then it is upcoming. IF [Next Discussion Due] is in the next fiscal year, then it is complete.
OR I misunderstand the question

Exaclty-- now here is what i figured to do for the fiscal year-- but when i enter in this expression and i go to test it access puts the "Completed" as a switch function almost -- so instead of "Completed" it adds [] so i have to enter in a date for completed

Status: IIf([Last Review Date]>=#31 OCT 2008#()Completed”,IIf([Next Discussion Due]<Date(),"Overdue”,IIf([Next Discussion Due]>=Date(),Upcoming”)))

any ideas of what i am doing wrong??

 

[KeithG]

So you are saying it is treating Completed as a parameter? Also is the above exactly how it is written in your query?

 

[king07]

The above expression is exactly how i have it written in my query (which i assume is the wrong way to have it). I do not want completed to be a parameter... i just want it to show completed if the discussion happened in the current fiscal year. How do i incorporate that?

 

[KeithG]

You need a beginning quotation on Completed.

 

[king07]

Keith.

I am now getting an invalid syntax error for using overdue.

Here is my exact expression...what am i doing wrong??

Status: IIf([Last Review Date]>=#31 OCT 2008#,"Completed”,IIf([Next Discussion Due]<=Date(),"Overdue”,IIf([Next Discussion Due]>=Date(),"Upcoming”)))

 

[DALeffler]

Status: IIf([Last Review Date]>=#31 OCT 2008#,"Completed”,IIf([Next Discussion Due]<=Date(),"Overdue”,IIf([Next Discussion Due]>=Date(),"Upcoming”)))

As a start, there isn't a false branch in the last Iif of the nested conditionals:

Status: IIf([Last Review Date]>=#31 OCT 2008#,"Completed”,IIf([Next Discussion Due]<=Date(),"Overdue”,IIf([Next Discussion Due]>=Date(),"Upcoming”,???)))

I believe you're going to need a record for every employee that details each review date for each fiscal year. If you don't have that, any "Last Review Date" less than the current date will logically show up as "Complete", when what I think you're after is the date of the review - if any - for each fiscal year.

But I have been known to answer questions that ain't been asked...

 

[king07]

I believe you're going to need a record for every employee that details each review date for each fiscal year. If you don't have that, any "Last Review Date" less than the current date will logically show up as "Complete", when what I think you're after is the date of the review - if any - for each fiscal year.


I have a record for every employee that has detailed each review date. the date is what i am after for each fiscal year to show whether or not it is complete or not. any auggestions??

Also, i tried the formula and again i get the operand error for overdue???

 

[king07]

Status: IIf([Last Review Date]>=#31 OCT 2008#,"Completed”,IIf([Next Discussion Due]<=Date(),"Overdue”,IIf([Next Discussion Due]>=Date(),"Upcoming”)))

Okay-- it has finally dawned on me why completed hasnt shown up-- with the iff statement of last review date>=#31 OCT 2008#-- this isnt specific enough-- i need completed to show up under status if last review date falls in between the dates 11/01/2006 and 10/31/2007-- any ideas of how to do an expression for that? i have looked numerous places and have been unable to locate anything--

any help????

 

[DALeffler]

Iif([LastReviewDate]>=#11/1/2006# And ([LastReviewDate]<=#10/31/2007#, "Complete", ...

 

[king07]

Okay Doug thanks BIG time for the help

Here is what i have as an expression

Status:IIf([Last Review Date]>=#11/1/2006# And ([Last Review Date]<=#10/31/2007#, "Completed",IIf([Next Discussion Due]<Date(),"OverDue",IIf([Next Discussion Due]>Date(),”UpComing”,””)))

and the error that now comes up is:

the expression you entered is missing a closing parenthesis,bracket or a vertical bar...

what am i doing wrong now???

 

[DALeffler]

Status: IIf([Last Review Date]>=#11/1/2006# And [Last Review Date]<=#10/31/2007#,"Completed",IIf([Next Discussion Due]<Date(),"OverDue",IIf([Next Discussion Due]>Date(),"UpComing","")))

 

[king07]

Thanks Doug

It worked!!