Iif statement

  • Thread starter Thread starter Alex Hammerstein
  • Start date Start date
A

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
 
First, tell us what you want this expression to do. Then we can evaluate the
syntax to see what might need to be changed.
 
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
 
Alex said:
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]))
Click to expand...
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.
 
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])

)


Alex said:
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]))
Click to expand...
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.
Click to expand...
 
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 said:
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]))
Click to expand...
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.
Click to expand...
 
It seems to - does it work?

Alex said:
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 said:
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]))
Click to expand...
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.
Click to expand...
Click to expand...
 
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?

Alex said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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
'====================================================


Alex said:
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.
Click to expand...
Click to expand...
 
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
'====================================================


Alex said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top