# Date Brackets as an IIf statement

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

G

#### 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

J

#### Jerry Whittle

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

J

#### John W. Vinson

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

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

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.