Solved

# Calculate dates in access 2010

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

0
Question by:Cyprexx IT

Author Comment

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.
0

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...
http://access.mvps.org/access/datetime/date0012.htm

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.

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.
0

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:
``````SELECT TOP 1
CalendarDate AS StorageBeginsOn
FROM Calendar
WHERE IsWorkDay = Yes
AND CalendarDate >= ({yourEntryDate} + 4);
``````
0