multiple outcomes

[Vadimbar]

Hello, I'm tring to run a query that calculates total # of days. I have two
fields [Scarif Date] and [Stratif Date]. I can have a value in either one or
both or none. [Plant age] would capture the # of days.
Example
If record has both [Scarif Date] and [Stratif Date] date values then [Plant
Age]=Date()-[Stratif Date]

If value has [Stratif Date] value but no [Scarif Date] date value then I
still need [Plant Age]=Date()-[Stratif Date]

If value has [Scarif Date] value and no [Stratif Date] value then I need
[Plant Age]=Date()-[Scarif Date]

How do I implement these 3 scenerios into a query?
I tried using the Iff function but I dont know how to implement all three
possible conditions with three different outcomes.

Thank you,
Vadimbar...

 

[George Nicholson]

How do I implement these 3 scenerios into a query?
You really only have 2 scenarios. 1 and 2 use the same formula, so build the
iif condition to handle #3 if True and let the Else take care of 1 and 2.

PlantAge: iif(nz([Scarif Date],0)=0,Date() - nz([ScarifDate],0),
Date()-nz([StratifDate],0)

 

[Vadimbar]

I mused have done something wrong?
This is my ouput:
Strat End Date Scarif End Date Plant Age
03/03/2008 01/01/2008 44
02/10/2008 02/02/2008 66
02/05/2008 39554
02/05/2008 39554

Code:
Plant Age: IIf(nz([Scarif End Date],0)=0,Date()-nz([Scarif End
Date],0),Date()-nz([Strat End Date],0))

How do I implement these 3 scenerios into a query?

You really only have 2 scenarios. 1 and 2 use the same formula, so build the
iif condition to handle #3 if True and let the Else take care of 1 and 2.

PlantAge: iif(nz([Scarif Date],0)=0,Date() - nz([ScarifDate],0),
Date()-nz([StratifDate],0)

--
HTH,
George

Hello, I'm tring to run a query that calculates total # of days. I have
two
fields [Scarif Date] and [Stratif Date]. I can have a value in either one
or
both or none. [Plant age] would capture the # of days.
Example
If record has both [Scarif Date] and [Stratif Date] date values then
[Plant
Age]=Date()-[Stratif Date]

If value has [Stratif Date] value but no [Scarif Date] date value then I
still need [Plant Age]=Date()-[Stratif Date]

If value has [Scarif Date] value and no [Stratif Date] value then I need
[Plant Age]=Date()-[Scarif Date]

How do I implement these 3 scenerios into a query?
I tried using the Iff function but I dont know how to implement all three
possible conditions with three different outcomes.

Thank you,
Vadimbar...

 

[Vadimbar]

The output is off here as well. :-(
Scarif End Date Strat End Date PlantAge
01/01/2008 03/03/2008 106
02/02/2008 02/10/2008 74
02/05/2008 ?
04/15/2008 1
04/15/2008 ?
----------------------------------------------------------------

start here:

SELECT IIf(Len([StratifDate]) < 1 And Len(ScarifDate) > 0,Date() -
[StratifDate],Date() -[ScarifDate]) AS PlantAge
FROM PlantTable;

Hello, I'm tring to run a query that calculates total # of days. I have two
fields [Scarif Date] and [Stratif Date]. I can have a value in either one or
both or none. [Plant age] would capture the # of days.
Example
If record has both [Scarif Date] and [Stratif Date] date values then [Plant
Age]=Date()-[Stratif Date]

If value has [Stratif Date] value but no [Scarif Date] date value then I
still need [Plant Age]=Date()-[Stratif Date]

If value has [Scarif Date] value and no [Stratif Date] value then I need
[Plant Age]=Date()-[Scarif Date]

How do I implement these 3 scenerios into a query?
I tried using the Iff function but I dont know how to implement all three
possible conditions with three different outcomes.

Thank you,
Vadimbar...

 

[George Nicholson]

I mused have done something wrong?
Nope, my bad. Try this:

Plant Age: IIf(nz([Stratif End Date],0)=0,Date()-nz([Scarif End
Date],0), Date()-nz([Stratif End Date],0))

Results: 44, 66, 71, 71

--
HTH,
George

I mused have done something wrong?
This is my ouput:
Strat End Date Scarif End Date Plant Age
03/03/2008 01/01/2008 44
02/10/2008 02/02/2008 66
02/05/2008 39554
02/05/2008 39554

Code:
Plant Age: IIf(nz([Scarif End Date],0)=0,Date()-nz([Scarif End
Date],0),Date()-nz([Strat End Date],0))

How do I implement these 3 scenerios into a query?

You really only have 2 scenarios. 1 and 2 use the same formula, so build
the
iif condition to handle #3 if True and let the Else take care of 1 and 2.

PlantAge: iif(nz([Scarif Date],0)=0,Date() - nz([ScarifDate],0),
Date()-nz([StratifDate],0)

--
HTH,
George

Hello, I'm tring to run a query that calculates total # of days. I have
two
fields [Scarif Date] and [Stratif Date]. I can have a value in either
one
or
both or none. [Plant age] would capture the # of days.
Example
If record has both [Scarif Date] and [Stratif Date] date values then
[Plant
Age]=Date()-[Stratif Date]

If value has [Stratif Date] value but no [Scarif Date] date value then
I
still need [Plant Age]=Date()-[Stratif Date]

If value has [Scarif Date] value and no [Stratif Date] value then I
need
[Plant Age]=Date()-[Scarif Date]

How do I implement these 3 scenerios into a query?
I tried using the Iff function but I dont know how to implement all
three
possible conditions with three different outcomes.

Thank you,
Vadimbar...

 

[John Spencer]

Seems to me that all you need if

AgeATE() - NZ([Stratif Date],[Scarif Date])

Alternative way to do this is
Age: Date()-IIF([Stratif Date] is not null,[Stratif Date],[Scarif Date])

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Vadimbar]

John thats excellent thank you!!!

Seems to me that all you need if

AgeATE() - NZ([Stratif Date],[Scarif Date])

Alternative way to do this is
Age: Date()-IIF([Stratif Date] is not null,[Stratif Date],[Scarif Date])

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hello, I'm tring to run a query that calculates total # of days. I have two
fields [Scarif Date] and [Stratif Date]. I can have a value in either one or
both or none. [Plant age] would capture the # of days.
Example
If record has both [Scarif Date] and [Stratif Date] date values then [Plant
Age]=Date()-[Stratif Date]

If value has [Stratif Date] value but no [Scarif Date] date value then I
still need [Plant Age]=Date()-[Stratif Date]

If value has [Scarif Date] value and no [Stratif Date] value then I need
[Plant Age]=Date()-[Scarif Date]

How do I implement these 3 scenerios into a query?
I tried using the Iff function but I dont know how to implement all three
possible conditions with three different outcomes.

Thank you,
Vadimbar...

 

[Vadimbar]

That worked! Thank you very much.

I mused have done something wrong?

Nope, my bad. Try this:

Plant Age: IIf(nz([Stratif End Date],0)=0,Date()-nz([Scarif End
Date],0), Date()-nz([Stratif End Date],0))

Results: 44, 66, 71, 71

--
HTH,
George

I mused have done something wrong?
This is my ouput:
Strat End Date Scarif End Date Plant Age
03/03/2008 01/01/2008 44
02/10/2008 02/02/2008 66
02/05/2008 39554
02/05/2008 39554

Code:
Plant Age: IIf(nz([Scarif End Date],0)=0,Date()-nz([Scarif End
Date],0),Date()-nz([Strat End Date],0))

How do I implement these 3 scenerios into a query?
You really only have 2 scenarios. 1 and 2 use the same formula, so build
the
iif condition to handle #3 if True and let the Else take care of 1 and 2.

PlantAge: iif(nz([Scarif Date],0)=0,Date() - nz([ScarifDate],0),
Date()-nz([StratifDate],0)

--
HTH,
George


Hello, I'm tring to run a query that calculates total # of days. I have
two
fields [Scarif Date] and [Stratif Date]. I can have a value in either
one
or
both or none. [Plant age] would capture the # of days.
Example
If record has both [Scarif Date] and [Stratif Date] date values then
[Plant
Age]=Date()-[Stratif Date]

If value has [Stratif Date] value but no [Scarif Date] date value then
I
still need [Plant Age]=Date()-[Stratif Date]

If value has [Scarif Date] value and no [Stratif Date] value then I
need
[Plant Age]=Date()-[Scarif Date]

How do I implement these 3 scenerios into a query?
I tried using the Iff function but I dont know how to implement all
three
possible conditions with three different outcomes.

Thank you,
Vadimbar...