Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

IIF Expressions in Query 2

Status
Not open for further replies.
Jan 16, 2007
4
CA
Okay Here goes..

I have a complicated question here. I am currently building a db for career development in my workplace. The main fields that i am having some issues with is the following. I have three fields labelled as the following:
1) Last Review date
2) Next review date ( i have this entered in as an auto generated date-- will add one year from the last review date)
3) Status

I have everything worked out except for the status field. Our year goes from nov 1-oct 31. So these are the options that i want to show up--

overdue-- if not completed in the fiscal year
completed-- if done in fiscal year
upcoming-- booked for a date within the fiscal year.

Here is my formuls so far::

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

When I try to close access i get the following error:


The expression you entered is missing a closing parenthesis bracket or vertical bar--

i cannot seem to fix this--

does anyone have any ideas??
 
Extra paren: [cpde]Status:Iif([LastReviewDate]>=#11/1/2006# And ([LastReviewDate]<=#10/31/2007#," [/code]

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill
 
And what about this ?
Status:IIf([LastReviewDate] Between #2006-11-01# And #2007-10-31#,'Complete',IIf([Next Discussion Due]<Date(),'OverDue',IIf([Next Discussion Due]>Date),'UpComing','?')))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Greg

Thanks for the quick response-- i have the extra paren there where you highlighted and i am still getting the following error when i attempt to close:

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


Can you see anything wrong with my expression??
 
PHV

YOU ARE A GOD!!!

Thank you so much, i have been struggling with this for about 2 weeks straight...

i bow down to you!!!

[afro]



 
PHV,
Does it ever get boring having throngs of people bowing down to your SQL godhood?

[rofl2]

Leslie
 
Leslie, my reply was much based on VBA skill than SQL ...
Note: the funnest thing is that I'm not a windows guy at all (have a look at my profile)...
 
Oh I know! It's just you get so much adulation for your SQL skills!
 
So, are you just suggesting me to not reply anymore in any SQL forum in order to protect my modesty ? ;-)
 
Okay

one more question...

(sorry just so happy that someone could FINALLY help with the last question...)

how do i encorporate Forecolor now?? would it be as simple as Iif([Last Review Date]>=#11/1/2006# And ([LastReviewDate]<=#10/31/2007#,"Complete",THEN forecolor=vbgreen?? or am i missing something???
 
SQL is designed for data retrieval, not for display.
If your actual context is a form then have a look at conditional formatting (ac2k or above).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top