Autopopulate Field

[Guest]

I am going to create a table where one field is the dates that a project was
worked on. I want to autopopulate another field that will fill in the Cycle
Peiod by whatever date was entered. For example, any project with any dates
between 1/1 - 1/31 will populate the text 'Cycle 1' in the autopopluate
field. 2/1 - 2/28 will populate 'Cycle 2' and so on. Plan is to use this
database for multiple years so would have to be just based on the month and
day, not year.

 

[John W. Vinson]

I am going to create a table where one field is the dates that a project was
worked on. I want to autopopulate another field that will fill in the Cycle
Peiod by whatever date was entered. For example, any project with any dates
between 1/1 - 1/31 will populate the text 'Cycle 1' in the autopopluate
field. 2/1 - 2/28 will populate 'Cycle 2' and so on. Plan is to use this
database for multiple years so would have to be just based on the month and
day, not year.

If the Cycle Period can always be derived from the date, then I'd say it
should not be stored in your table AT ALL. Just calculate it on demand. What's
the algorithm? Are all dates in any January Cycle 1, all dates in May Cycle 5
and so on? or does it get trickier after February 28 (or 29th in leap years)?

John W. Vinson [MVP]

 

[Guest]

I actually miread the report they currently are manually using. Each WEEK is
a different Cycle Period. Not each month.

 

[John W. Vinson]

I actually miread the report they currently are manually using. Each WEEK is
a different Cycle Period. Not each month.

Again: what's the algorithm?

You might want to consider either using the DatePart function:

DatePart("ww", [datefield], <optional arguments, see the help>)

to calculate the week-of-the-year, or set up a Cycles table with two fields -
every date in the year and the corresponding cycle for that date.

John W. Vinson [MVP]