Prev: QBE
Next: Unable to use filter in datasheet view or add criteria to query in design view
From: alecgreen on 3 Mar 2010 02:45
Hi

I am trying to create a nested iif, for the following scenerio - but I
am having difficulties

if due date is less than today, show yesterdays date.
if due date is between today and today plus 14 days, show the current
due date.
if due date is greater than today plus 14 days, show the current due
date plus 15 days.

I have managed the basic iif (true/false), but cant evaluate the rest!

Many Thanks

Alec
From: Krzysztof Naworyta on 3 Mar 2010 03:32
alecgreen wrote:

| I am trying to create a nested iif, for the following scenerio - but I
| am having difficulties
|
| if due date is less than today, show yesterdays date.
| if due date is between today and today plus 14 days, show the current
| due date.
| if due date is greater than today plus 14 days, show the current due
| date plus 15 days.

IIF([Field1]<date();
date()-1;
IIF(Field1]>=date() and [Field1]<=date()+14;
date();
date()+15
)
)

--
KN
From: alecgreen on 3 Mar 2010 05:55
On 3 Mar, 08:32, "Krzysztof Naworyta" <k.nawor...(a)datacomp.com.pl>
wrote:
> alecgreen wrote:
>
> | I am trying to create a nested iif, for the following scenerio - but I
> | am having difficulties
> |
> | if due date is less than today, show yesterdays date.
> | if due date is between today and today plus 14 days, show the current
> | due date.
> | if due date is greater than today plus 14 days, show the current due
> | date plus 15 days.
>
> IIF([Field1]<date();
>         date()-1;
>         IIF(Field1]>=date() and [Field1]<=date()+14;
>             date();
>             date()+15
>             )
>     )
>
> --
> KN

Brilliant, Thanks
 | 
Pages: 1
Prev: QBE
Next: Unable to use filter in datasheet view or add criteria to query in design view