If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Date Brackets as an IIf statement
Looking for help writing two IIF queries that will put some dates into
brackets. I have some sku's that have a expiration date and the idea is to put the product into 1 of 5 brackets. The brackets a Brackets: Age Date Bracket Code 1. Age Date Bracket Desc "Greater than 6 months left", = 181 days Age Date Bracket Code 2. Age Date Bracket Desc "6 months to 3 months" = 91 up to 180 days Age Date Bracket Code 3. Age Date Bracket Desc "3 months to 1 month" = 31 up to 90 days Age Date Bracket Code 4. Age Date Bracket Desc "1 month left" = 1 up to 30 Age Date Bracket Code 5. Age Date Bracket Desc "expired" = =0 I have the following in my data: Production Date, Shelf Life, and Expiration Date. Can someone help me make two fields: Age Date Bracket Code and a Age Date Bracket Description? Test Data is as follows: Production DT Shelf Life Expiration DT Age DT CD Age DT DESC 12/22/2009 365 12/20/2010 1 New Product 10/22/2009 365 10/22/2010 1 New Product 10/21/2009 365 10/21/2010 2 Mild, 6 to 3 months left 06/21/2009 365 06/21/2010 2 Mild, 6 to 3 months left 06/20/2009 365 06/20/2010 3 Moderat, 3 to 1 months left 05/22/2009 365 05/20/2010 3 Moderat, 3 to 1 months left 05/21/2009 365 05/21/2010 4 Critical, 1 months left 10/24/2009 180 04/22/2010 4 Critical, 1 months left 10/23/2009 180 04/21/2010 5 Expired Any help create a IIF statement in my query is greatly appreciated. Regards, Gary |
#2
|
|||
|
|||
Date Brackets as an IIf statement
NOTE: I have wrote my statement as follows:
Age_DT_CD: IIf([# of Days left]=0,"5",IIf([# of Days left] Between 0 And 30,"4",IIf([# of Days left] Between 31 And 90,"3"),IIf([# of Days left] Between 91 And 180,"2"),IIf([# of Days left] 180,"1"))))) Currently "Gary" wrote: Looking for help writing two IIF queries that will put some dates into brackets. I have some sku's that have a expiration date and the idea is to put the product into 1 of 5 brackets. The brackets a Brackets: Age Date Bracket Code 1. Age Date Bracket Desc "Greater than 6 months left", = 181 days Age Date Bracket Code 2. Age Date Bracket Desc "6 months to 3 months" = 91 up to 180 days Age Date Bracket Code 3. Age Date Bracket Desc "3 months to 1 month" = 31 up to 90 days Age Date Bracket Code 4. Age Date Bracket Desc "1 month left" = 1 up to 30 Age Date Bracket Code 5. Age Date Bracket Desc "expired" = =0 I have the following in my data: Production Date, Shelf Life, and Expiration Date. Can someone help me make two fields: Age Date Bracket Code and a Age Date Bracket Description? Test Data is as follows: Production DT Shelf Life Expiration DT Age DT CD Age DT DESC 12/22/2009 365 12/20/2010 1 New Product 10/22/2009 365 10/22/2010 1 New Product 10/21/2009 365 10/21/2010 2 Mild, 6 to 3 months left 06/21/2009 365 06/21/2010 2 Mild, 6 to 3 months left 06/20/2009 365 06/20/2010 3 Moderat, 3 to 1 months left 05/22/2009 365 05/20/2010 3 Moderat, 3 to 1 months left 05/21/2009 365 05/21/2010 4 Critical, 1 months left 10/24/2009 180 04/22/2010 4 Critical, 1 months left 10/23/2009 180 04/21/2010 5 Expired Any help create a IIF statement in my query is greatly appreciated. Regards, Gary |
#3
|
|||
|
|||
Date Brackets as an IIf statement
Once you next about 3 IIf's, things get hard to maintain. Instead you can use
Select Case within a function inside a module. Then you can call upon it in a query/SQL statement. Below is an example. Function fTiers(strTiers As Variant) As String Dim TheTier As String Select Case strTiers Case Is 1 TheTier = "Greater than 6 months left" Case = 2 TheTier = "6 months to 3 months" Case = 3 TheTier = "3 months to 1 month" Case = 4 TheTier = "1 month left" Case = 5 TheTier = "expired" Case Else ' Other values. TheTier = "Not Tier" End Select fTiers = TheTier End Function Then in the query field put something like: Age Date Bracket Desc: fTiers([Age DT CD]) -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Gary" wrote: Looking for help writing two IIF queries that will put some dates into brackets. I have some sku's that have a expiration date and the idea is to put the product into 1 of 5 brackets. The brackets a Brackets: Age Date Bracket Code 1. Age Date Bracket Desc "Greater than 6 months left", = 181 days Age Date Bracket Code 2. Age Date Bracket Desc "6 months to 3 months" = 91 up to 180 days Age Date Bracket Code 3. Age Date Bracket Desc "3 months to 1 month" = 31 up to 90 days Age Date Bracket Code 4. Age Date Bracket Desc "1 month left" = 1 up to 30 Age Date Bracket Code 5. Age Date Bracket Desc "expired" = =0 I have the following in my data: Production Date, Shelf Life, and Expiration Date. Can someone help me make two fields: Age Date Bracket Code and a Age Date Bracket Description? Test Data is as follows: Production DT Shelf Life Expiration DT Age DT CD Age DT DESC 12/22/2009 365 12/20/2010 1 New Product 10/22/2009 365 10/22/2010 1 New Product 10/21/2009 365 10/21/2010 2 Mild, 6 to 3 months left 06/21/2009 365 06/21/2010 2 Mild, 6 to 3 months left 06/20/2009 365 06/20/2010 3 Moderat, 3 to 1 months left 05/22/2009 365 05/20/2010 3 Moderat, 3 to 1 months left 05/21/2009 365 05/21/2010 4 Critical, 1 months left 10/24/2009 180 04/22/2010 4 Critical, 1 months left 10/23/2009 180 04/21/2010 5 Expired Any help create a IIF statement in my query is greatly appreciated. Regards, Gary |
#4
|
|||
|
|||
Date Brackets as an IIf statement
On Wed, 21 Apr 2010 10:24:03 -0700, Gary
wrote: Looking for help writing two IIF queries that will put some dates into brackets. I have some sku's that have a expiration date and the idea is to put the product into 1 of 5 brackets. The brackets a Brackets: Age Date Bracket Code 1. Age Date Bracket Desc "Greater than 6 months left", = 181 days Age Date Bracket Code 2. Age Date Bracket Desc "6 months to 3 months" = 91 up to 180 days Age Date Bracket Code 3. Age Date Bracket Desc "3 months to 1 month" = 31 up to 90 days Age Date Bracket Code 4. Age Date Bracket Desc "1 month left" = 1 up to 30 Age Date Bracket Code 5. Age Date Bracket Desc "expired" = =0 I have the following in my data: Production Date, Shelf Life, and Expiration Date. Can someone help me make two fields: Age Date Bracket Code and a Age Date Bracket Description? Test Data is as follows: Production DT Shelf Life Expiration DT Age DT CD Age DT DESC 12/22/2009 365 12/20/2010 1 New Product 10/22/2009 365 10/22/2010 1 New Product 10/21/2009 365 10/21/2010 2 Mild, 6 to 3 months left 06/21/2009 365 06/21/2010 2 Mild, 6 to 3 months left 06/20/2009 365 06/20/2010 3 Moderat, 3 to 1 months left 05/22/2009 365 05/20/2010 3 Moderat, 3 to 1 months left 05/21/2009 365 05/21/2010 4 Critical, 1 months left 10/24/2009 180 04/22/2010 4 Critical, 1 months left 10/23/2009 180 04/21/2010 5 Expired Any help create a IIF statement in my query is greatly appreciated. Regards, Gary I would suggest that IIF is simply the wrong tool for the job. You could use VBA as Gary suggests, or - perhaps even better - use a table-driven solution. Create a table with the days cutoff and the label for values less than that number of days. You can then use an expression like DateAdd("d", [Shelf Life], [Production Date]) to calculate the expiration date, and DateDiff("d", DateAdd("d", [Shelf Life], [Production Date]), Date()) to calculate the number of days until (or past) the expiration. Join this field to your table of labels to get the appropriate label for today. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|