470,584 Members | 2,339 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,584 developers. It's quick & easy.

Expression

3
I am trying to write an expression and having trouble. I get the first part of my arguments correct but when I try to add the third I run into trouble. Here is the first part of the expression:
Expand|Select|Wrap|Line Numbers
  1. =IIf([completedate]>"",
  2.    "Complete",
  3.    IIf(
  4.       IsNull([CompleteDate]) And ([DueDate]<Date()),
  5.       "Overdue",
  6.       ""
  7.    )
  8. )
This works correctly. When I try and add the last bit it does not work. Here is what I am trying to add

IIf([ReviewDate]<= Date(),"In Process","")

Can anyone help me.
Apr 1 '22 #1
2 16241
cactusdata
199 Expert 128KB
Try with:

Expand|Select|Wrap|Line Numbers
  1. =IIf(Not [CompleteDate] Is Null,
  2.     "Complete",
  3.     IIf([ReviewDate] <= Date(), 
  4.         "In Process",
  5.         IIf([DueDate] < Date()
  6.             "Overdue",
  7.             Null)))
Apr 1 '22 #2
zmbd
5,436 Expert Mod 4TB
@Teri B
When writing nested IIF() functions I usually start out by creating the skeleton code
(would you post back which database you're using)
Expand|Select|Wrap|Line Numbers
  1. Start with the outer IIF - notice the stepping of the code 
  2. stepping your code will help keep things straight in you mind
  3. IIF(condition, 
  4.    true, 
  5.    false
  6. )
  7. Now add the inner IIF(); in this example an on false 
  8. IIF(condition, 
  9.    true, 
  10.    IIF(condition2
  11.       true,
  12.       false
  13.    )
  14. )
  15. at this point keep adding and stepping
  16. - say I want a on true for the 3rd
  17. IIF(condition, 
  18.    true, 
  19.    IIF(condition2
  20.       IFF(condition3, 
  21.          true, 
  22.          false
  23.       ),
  24.       false
  25.    )
  26. )
  27.  
  28. quite often in VBA I'll comment the code as I go; unfortunately,
  29. most SQL will not let you add comments
  30. IIF(condition1,
  31.    'on true event condition1 
  32.    true, 
  33.    'on false event condition1 
  34.    IIF(condition2
  35.         'on true event condition2 
  36.       IFF(condition3,
  37.         'on true event condition3
  38.          true, 
  39.         'on false event condition3
  40.          false
  41.       ),
  42.       'on false event condition2
  43.       false
  44.    )
  45. )
  46. From this point, now that the skeleton is laid out, 
  47. insert your true and false actions at each branch 
  48. within the nested functions
  49.  
4 Weeks Ago #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

23 posts views Thread by Paul Rubin | last post: by
70 posts views Thread by Roy Yao | last post: by
22 posts views Thread by Tony Johansson | last post: by
2 posts views Thread by Mike Turco | last post: by
14 posts views Thread by John Temples | last post: by
7 posts views Thread by Billa | last post: by
1 post views Thread by lovecreatesbea... | last post: by
28 posts views Thread by Marc Gravell | last post: by
18 posts views Thread by dspfun | last post: by
1 post views Thread by livre | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.