Calculate dates in access 2010

Posted on 2011-10-30
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
    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

    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

    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

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now