Date Brackets as an IIf statement

  • Thread starter Thread starter Gary
  • Start date Start date
G

Gary

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 are:

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
 
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
 
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])
 
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 are:

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
Click to expand...

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.
 
Back
Top