Iif statement

[Alex Hammerstein]

Hi
Can someone tell me what is wrong with the syntax below please. I am not
very good with Iif statements

Weeks: IIf(IsNull([Date left]),DateDiff("ww",IIf([Date
Started]>#01/04/2008#,[Date
Started],Now()),#01/04/2008#,Now()),DateDiff("ww",IIf([Date
Started]>#01/04/2008#,[Date Started],Now()),#01/04/2008#,[Date left]))


Thanks

A

 

[Ken Snell MVP]

First, tell us what you want this expression to do. Then we can evaluate the
syntax to see what might need to be changed.

 

[Alex Hammerstein]

Where [date left] is Null, I need to calculate the date differential in
weeks; between either 01/04/08 or [Date Started] (which is the later) and
today's date.
else
Where [date left] is not null, I need to calculate the date differential in
weeks; between either 01/04/08 or [Date Started] (which is the later) and
[Date Left]

Thanks for your help

A

 

[Bob Barrows]

Hi
Can someone tell me what is wrong with the syntax below please. I am
not very good with Iif statements

Weeks: IIf(IsNull([Date left]),DateDiff("ww",IIf([Date
Started]>#01/04/2008#,[Date
Started],Now()),#01/04/2008#,Now()),DateDiff("ww",IIf([Date
Started]>#01/04/2008#,[Date Started],Now()),#01/04/2008#,[Date left]))

I find it helpful to break out each argument onto its own indented line.
Like this:

Weeks:
IIf(
IsNull([Date left]), <==arg1
DateDiff( <==beginnig of arg2
"ww", Datediff arg1
IIf(
[Date Started]>#01/04/2008#, <==arg1
[Date Started], <==arg2
Now() <==arg3
), Datediff arg2
#01/04/2008# <== Datediff arg3

First problem: you need to close the parentheses for the DateDiff function
call so let's do that:
) <==end of arg2
,Now() <== arg3 - this should be the end of the statement
)

However, you start another argument here ... Iif only takes 3 arguments: no
more, no less
,DateDiff("ww",IIf([Date
Started]>#01/04/2008#,[Date Started],Now()),#01/04/2008#,[Date left]))

So that's what's wrong with this Iif statement. How to fix it depends on
your answer to Ken's question.

 

[Alex Hammerstein]

Hi Bob

Very many thanks for your help
Does the following make sense now?

Weeks:
IIf(
IsNull([Date left]),
DateDiff( "ww", IIf([Date Started]>#01/04/2008#),[Date Started], Now()),
DateDiff( "ww", IIf([Date Started]>#01/04/2008#),[Date Started], [Date
Left])

)

Hi
Can someone tell me what is wrong with the syntax below please. I am
not very good with Iif statements

Weeks: IIf(IsNull([Date left]),DateDiff("ww",IIf([Date
Started]>#01/04/2008#,[Date
Started],Now()),#01/04/2008#,Now()),DateDiff("ww",IIf([Date
Started]>#01/04/2008#,[Date Started],Now()),#01/04/2008#,[Date left]))

I find it helpful to break out each argument onto its own indented line.
Like this:

Weeks:
IIf(
IsNull([Date left]), <==arg1
DateDiff( <==beginnig of arg2
"ww", Datediff arg1
IIf(
[Date Started]>#01/04/2008#, <==arg1
[Date Started], <==arg2
Now() <==arg3
), Datediff arg2
#01/04/2008# <== Datediff arg3

First problem: you need to close the parentheses for the DateDiff function
call so let's do that:
) <==end of arg2
,Now() <== arg3 - this should be the end of the statement
)

However, you start another argument here ... Iif only takes 3 arguments: no
more, no less
,DateDiff("ww",IIf([Date
Started]>#01/04/2008#,[Date Started],Now()),#01/04/2008#,[Date left]))

So that's what's wrong with this Iif statement. How to fix it depends on
your answer to Ken's question.

 

[Alex Hammerstein]

Hi Bob

Thanks for your help

Does the following make more sense given what I am trying to do:

Weeks:
IIf(
IsNull([Date left]),
DateDiff( "ww", IIf([Date Started]>#01/04/2008#),[Date Started], Now()),
DateDiff( "ww", IIf([Date Started]>#01/04/2008#),[Date Started], [Date
Left])
)

Alex

Hi
Can someone tell me what is wrong with the syntax below please. I am
not very good with Iif statements

Weeks: IIf(IsNull([Date left]),DateDiff("ww",IIf([Date
Started]>#01/04/2008#,[Date
Started],Now()),#01/04/2008#,Now()),DateDiff("ww",IIf([Date
Started]>#01/04/2008#,[Date Started],Now()),#01/04/2008#,[Date left]))

I find it helpful to break out each argument onto its own indented line.
Like this:

Weeks:
IIf(
IsNull([Date left]), <==arg1
DateDiff( <==beginnig of arg2
"ww", Datediff arg1
IIf(
[Date Started]>#01/04/2008#, <==arg1
[Date Started], <==arg2
Now() <==arg3
), Datediff arg2
#01/04/2008# <== Datediff arg3

First problem: you need to close the parentheses for the DateDiff function
call so let's do that:
) <==end of arg2
,Now() <== arg3 - this should be the end of the statement
)

However, you start another argument here ... Iif only takes 3 arguments: no
more, no less
,DateDiff("ww",IIf([Date
Started]>#01/04/2008#,[Date Started],Now()),#01/04/2008#,[Date left]))

So that's what's wrong with this Iif statement. How to fix it depends on
your answer to Ken's question.

 

[Bob Barrows]

It seems to - does it work?

Hi Bob

Thanks for your help

Does the following make more sense given what I am trying to do:

Weeks:
IIf(
IsNull([Date left]),
DateDiff( "ww", IIf([Date Started]>#01/04/2008#),[Date Started],
Now()), DateDiff( "ww", IIf([Date Started]>#01/04/2008#),[Date
Started], [Date Left])
)

Alex

Hi
Can someone tell me what is wrong with the syntax below please. I
am
not very good with Iif statements

Weeks: IIf(IsNull([Date left]),DateDiff("ww",IIf([Date
Started]>#01/04/2008#,[Date
Started],Now()),#01/04/2008#,Now()),DateDiff("ww",IIf([Date
Started]>#01/04/2008#,[Date Started],Now()),#01/04/2008#,[Date
left]))

I find it helpful to break out each argument onto its own indented
line. Like this:

Weeks:
IIf(
IsNull([Date left]), <==arg1
DateDiff( <==beginnig of arg2
"ww", Datediff arg1
IIf(
[Date Started]>#01/04/2008#, <==arg1
[Date Started], <==arg2
Now() <==arg3
), Datediff arg2
#01/04/2008# <== Datediff arg3

First problem: you need to close the parentheses for the DateDiff
function call so let's do that:
) <==end of arg2
,Now() <== arg3 - this should be the end of the statement
)

However, you start another argument here ... Iif only takes 3
arguments: no more, no less
,DateDiff("ww",IIf([Date
Started]>#01/04/2008#,[Date Started],Now()),#01/04/2008#,[Date
left]))

So that's what's wrong with this Iif statement. How to fix it
depends on your answer to Ken's question.

 

[Alex Hammerstein]

No, Its not returning any data for some reason.

However the following did !!!

Weeks:
IIf(
IsNull([Date left]),
IIf ([Date Started] > #31/03/2008#, DateDiff("ww", [Date Started],
Now()), DateDiff("ww", #01/04/08#, Now())),
IIf ([Date Started] > #31/03/2008#, DateDiff("ww", [Date Started], [Date
Left]), DateDiff("ww", #01/04/08#, [Date left]))
)

Thanks for your help

A

It seems to - does it work?

Hi Bob

Thanks for your help

Does the following make more sense given what I am trying to do:

Weeks:
IIf(
IsNull([Date left]),
DateDiff( "ww", IIf([Date Started]>#01/04/2008#),[Date Started],
Now()), DateDiff( "ww", IIf([Date Started]>#01/04/2008#),[Date
Started], [Date Left])
)

Alex

Alex Hammerstein wrote:
Hi
Can someone tell me what is wrong with the syntax below please. I
am
not very good with Iif statements

Weeks: IIf(IsNull([Date left]),DateDiff("ww",IIf([Date
Started]>#01/04/2008#,[Date
Started],Now()),#01/04/2008#,Now()),DateDiff("ww",IIf([Date
Started]>#01/04/2008#,[Date Started],Now()),#01/04/2008#,[Date
left]))


I find it helpful to break out each argument onto its own indented
line. Like this:

Weeks:
IIf(
IsNull([Date left]), <==arg1
DateDiff( <==beginnig of arg2
"ww", Datediff arg1
IIf(
[Date Started]>#01/04/2008#, <==arg1
[Date Started], <==arg2
Now() <==arg3
), Datediff arg2
#01/04/2008# <== Datediff arg3

First problem: you need to close the parentheses for the DateDiff
function call so let's do that:
) <==end of arg2
,Now() <== arg3 - this should be the end of the statement
)

However, you start another argument here ... Iif only takes 3
arguments: no more, no less
,DateDiff("ww",IIf([Date
Started]>#01/04/2008#,[Date Started],Now()),#01/04/2008#,[Date
left]))

So that's what's wrong with this Iif statement. How to fix it
depends on your answer to Ken's question.

 

[John Spencer]

Perhaps you can use this expression to calculate what you want.

DateDiff("ww"
, IIF([Date Started]>#01/04/08#,[Date Started],#01/04/08#)
, Nz([Date Left],Date()))

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Where [date left] is Null, I need to calculate the date differential in
weeks; between either 01/04/08 or [Date Started] (which is the later) and
today's date.
else
Where [date left] is not null, I need to calculate the date differential in
weeks; between either 01/04/08 or [Date Started] (which is the later) and
[Date Left]

Thanks for your help

A

First, tell us what you want this expression to do. Then we can evaluate the
syntax to see what might need to be changed.

 

[Alex Hammerstein]

John

Thank you so much for your posting.

I have to admit I don't understand why that works, I think I need to look up
the Nz function!

Alex

Perhaps you can use this expression to calculate what you want.

DateDiff("ww"
, IIF([Date Started]>#01/04/08#,[Date Started],#01/04/08#)
, Nz([Date Left],Date()))

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Where [date left] is Null, I need to calculate the date differential in
weeks; between either 01/04/08 or [Date Started] (which is the later) and
today's date.
else
Where [date left] is not null, I need to calculate the date differential in
weeks; between either 01/04/08 or [Date Started] (which is the later) and
[Date Left]

Thanks for your help

A

First, tell us what you want this expression to do. Then we can evaluate the
syntax to see what might need to be changed.