Calculate dates in access 2010

Posted on 2011-10-30
Medium Priority
Last Modified: 2012-05-12
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.

Question by:Cyprexx IT

Author Comment

by:Cyprexx IT
ID: 37054086
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.
LVL 77

Expert Comment

ID: 37055642
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.
LVL 22

Accepted Solution

8080_Diver earned 2000 total points
ID: 37056667
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


Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Audit trails are very important in any system to hold people responsible for certain transactions and hold them to take ownership of their actions. This article is dedicated to all novice "Microsoft Access" developers.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question