Period End Dates Table ... How to Link?

[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?

 

[Guest]

Try these two queries using your table name instead of Change Request and
field Date Open ---
Period_List ---
SELECT Period_Table.Period, Period_Table.[End Date], Period_Table_1.Period,
Period_Table_1.[End Date]
FROM Period_Table, Period_Table AS Period_Table_1
WHERE (((Period_Table_1.Period)=[Period_Table].[Period]+1));

SELECT [Change Requests].[Date open], Period_List.Period_Table.Period
FROM [Change Requests], Period_List
WHERE (((Period_List.Period_Table.[End Date])<=[Date open]) AND
((Period_List.Period_Table_1.[End Date])>=[Date open]));

 

[Bob Quintal]

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/0
7#,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?

Create a query of your periods table to force ascending date order,
DO NOT rely on an index in the table. Then use a dlookup in a query
or as the control source of a textbox on the form myPeriod:
Dlookup("period","qryperiods","[End Date] > #" & [mydate] & " #")

Aternatively use a subquery
myPeriod: (SELECT top 1 period from qryperiods WHERE [End DATE] >
[tablename].[mydate])

Also be careful using date as a field or control name, date is a
reserved word in Access, and Access often confuses field names and
the function or property.

 

[Greg]

That worked perfectly ... thanks for the help.

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/0
7#,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?

Create a query of your periods table to force ascending date order,
DO NOT rely on an index in the table. Then use a dlookup in a query
or as the control source of a textbox on the form myPeriod:
Dlookup("period","qryperiods","[End Date] > #" & [mydate] & " #")

Aternatively use a subquery
myPeriod: (SELECT top 1 period from qryperiods WHERE [End DATE] >
[tablename].[mydate])

Also be careful using date as a field or control name, date is a
reserved word in Access, and Access often confuses field names and
the function or property.