G
Greg
I need to figure out how to link a table of Period End Dates with a date
entry field. When an entry is made, it should pick up the period # based on
that date entry.
Period Table
Period End Date
1 1/21/2007
2 2/18/2007
3 3/18/2007
4 4/15/2007
5 5/13/2007
6 6/10/2007
7 7/8/2007
8 8/5/2007
9 9/2/2007
10 9/30/2007
11 10/28/2007
12 11/25/2007
13 12/31/2007
For example:
When the date of 4/01/2007 is entered in the data entry table, it should
pick up Period #4. 11/13/2007 should pick up Period #12. In the past I
have simply inserted the following formula in a query to extract this:
Period:
IIf([date]<#1/22/07#,1,IIf([date]<#2/19/07#,2,IIf([date]<#3/19/07#,3,IIf([date]<#4/16/07#,4,IIf([date]<#5/14/07#,5,IIf([date]<#6/11/07#,6,IIf([date]<#7/9/07#,7,IIf([date]<#8/06/07#,8,IIf([date]<#9/03/07#,9,IIf([date]<#10/01/07#,10,IIf([date]<#10/29/07#,11,IIf([date]<#11/26/07#,12,13))))))))))))
The problem with this method is that the period end dates change every year
and I have to go and change the formula in many different databases and I
will invariably miss some and it is subject to greater entry error.
Any thoughts on how I can either link the period table with other tables or
how I can phrase a formula to access the appropriate record in the Period
Table?
entry field. When an entry is made, it should pick up the period # based on
that date entry.
Period Table
Period End Date
1 1/21/2007
2 2/18/2007
3 3/18/2007
4 4/15/2007
5 5/13/2007
6 6/10/2007
7 7/8/2007
8 8/5/2007
9 9/2/2007
10 9/30/2007
11 10/28/2007
12 11/25/2007
13 12/31/2007
For example:
When the date of 4/01/2007 is entered in the data entry table, it should
pick up Period #4. 11/13/2007 should pick up Period #12. In the past I
have simply inserted the following formula in a query to extract this:
Period:
IIf([date]<#1/22/07#,1,IIf([date]<#2/19/07#,2,IIf([date]<#3/19/07#,3,IIf([date]<#4/16/07#,4,IIf([date]<#5/14/07#,5,IIf([date]<#6/11/07#,6,IIf([date]<#7/9/07#,7,IIf([date]<#8/06/07#,8,IIf([date]<#9/03/07#,9,IIf([date]<#10/01/07#,10,IIf([date]<#10/29/07#,11,IIf([date]<#11/26/07#,12,13))))))))))))
The problem with this method is that the period end dates change every year
and I have to go and change the formula in many different databases and I
will invariably miss some and it is subject to greater entry error.
Any thoughts on how I can either link the period table with other tables or
how I can phrase a formula to access the appropriate record in the Period
Table?