Calculate dates in access 2010

I'm completely clueless on how to use vba in the background to calculate some dates.
I have 7 fields that this needs to be done too. Some fields need to exclude weekends and holidays and one field needs to find the nearest Tuesday and find the nearest Thursday.

For example.

Field 1 -  DayWorkCompleted = Date input by user, ex September 1,2011
Field 2 -  StorageBeginsOn = 4th business day excluding weekends and holidays - September 7
Field 3 -  PublicationDate = Will vary per County - Septemebr 13
Field 4 -  PublishedDate = Date newspaper will publish the legal notice - September 15
Field 5 -  AuctionDate = 18 complete days from the date of the published legal notice - October 3
Field 6 -  NOCSent = Certified letters must be sent at least 15 prior to the auction date - September 16

I need this to calculate automatically once a date is entered into DayWorkCompleted. I'm completely clueless how to do this.

Cyprexx ITAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Cyprexx ITAuthor Commented:
I did the whole thing In excel, I just someone to tell me how to convert it, it's on standard expression for each field. I need an example formula, it's like asking me to breakup a registration form, won't work.
If you are new to Access then be completely aware that Access is NOT Excel.  The table datasheet view in Access may LOOK like a spreadsheet layout but that's where the similarity ends.

There are no standard functions in Access that correspond to say Excel's Workday() function.
Calculating future business days is a complicated business and requires substantial code.
You will find an example here...

Adding a number of days to a date IS simple..

newdate= Mydatefieldname+8
gives a result that adds 8 days to the value in MyDatefield.
(But this is not adding business days; just days)

Also all of the calculations must be done in a form based on your table.
You don't do this in the table itself.

It's not clear where your values for field3 and field4 come from.
Weekends, Holidays, and other "non-working" days are best handled by creating a table that has indicators for each type of classification.  For example, assuming a table name of Calendar, you might have the following columns:
CalendarDate (Date/Time datatype) - Primary Key;
IsWeekday (Yes/No datatype);
IsWorkday (Yes/No datatype);
IsHoliday (Yes/No datatype);

If you need someother designation, e.g. for a Canadian Holiday, you can simply add another Yes/No column, e.g. IsCanadianHoliday (Yes/No datatype);.

Then you can simply join to that table using your suppplied date and obtain the appropriate date.  For example:
             CalendarDate AS StorageBeginsOn
FROM Calendar
WHERE IsWorkDay = Yes
AND CalendarDate >= ({yourEntryDate} + 4);

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.