Update table with all dates for next 2 years - excluding weekends

[tezread]

I have a table called 'meeting' and a column in it called date. I want to add records to the table in one go so there is a record of every workday for the next 2 years:

i.e

Record 1. 3/8/2010
Record 2. 4/8/2010

etc

is there a quick way of doing?

 

[ted.martin]

The principle of what you are asking could be done by using some sort of Loop for 365*2 days = 2 years BUT what about Christmas and Weekends etc. You would need to define a workday.

The simplest may be to do it on an Excell spreadsheet where for a Monday to Friday sequence, the Monday code is previous cell + 3 to get you a Monday. If it needs to go into Access, then import it from Excel. Just create (say) a months worth of cells and then copy 24 times to get 2 years.

Monday 40392 ..... This is the start date
Tuesday =B1+1
Wednesday =B2+1
Thursday =B3+1
Friday =B4+1
Monday =B5+3
Tuesday =B6+1
Wednesday =B7+1
Thursday =B8+1
Friday =B9+1

 

[tezread]

The principle of what you are asking could be done by using some sort of Loop for 365*2 days = 2 years BUT what about Christmas and Weekends etc. You would need to define a workday.

The simplest may be to do it on an Excell spreadsheet where for a Monday to Friday sequence, the Monday code is previous cell + 3 to get you a Monday. If it needs to go into Access, then import it from Excel. Just create (say) a months worth of cells and then copy 24 times to get 2 years.

Monday 40392 ..... This is the start date
Tuesday =B1+1
Wednesday =B2+1
Thursday =B3+1
Friday =B4+1
Monday =B5+3
Tuesday =B6+1
Wednesday =B7+1
Thursday =B8+1
Friday =B9+1

Thank you


I have created a month worth of cells but when I copy and paste it pastes the same dates

 

[tezread]

sorted now - thank you

 

[ted.martin]

You are welcome; as always, its important to choose the best tools for the job and in this instance, Excel is more user friendly for what was essentially a simple calculation.

 

[DCrake]

So in 2 years you will have to repeat this exercise. Well take a look at this sample database it covers the next 20 years and has all sorts of intellisense regarding dates.