 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / April 2008multiple outcomes
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... > 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)  SignatureHTH, George > Hello, I'm tring to run a query that calculates total # of days. I have > two [quoted text clipped - 18 lines] > Thank you, > 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 [quoted text clipped - 25 lines] > > Thank you, > > Vadimbar... >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 SignatureHTH, George >I mused have done something wrong? > This is my ouput: [quoted text clipped - 42 lines] >> > Thank you, >> > Vadimbar... That worked! Thank you very much. > >I mused have done something wrong? > Nope, my bad. Try this: [quoted text clipped - 50 lines] > >> > Thank you, > >> > Vadimbar... 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 [quoted text clipped - 15 lines] >Thank you, >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: > [quoted text clipped - 21 lines] > >Thank you, > >Vadimbar... Seems to me that all you need if Age:DATE() - 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 [quoted text clipped - 15 lines] > Thank you, > Vadimbar... John thats excellent thank you!!! > Seems to me that all you need if > [quoted text clipped - 29 lines] > > Thank you, > > Vadimbar... |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.