Solved

access 2010 vba calculate dates

Posted on 2014-04-24
4
646 Views
Last Modified: 2014-04-24
Hi,

I have a query regarding using vba to calculate due dates.

Overview:
i have a form where the user enters an start date for a contract. What i need to do is, after the start date has been entered, auto calculate 2 different dates based upon the month of the start date.

i.e. if the start date entered is any day in January, for example 05/01/2014, then due date to be calculated should be 01/03/2014, settlement date to be calculated should be 31/03/2014.

Start Date                Due Date             Settlement Date
any day in Jan           01/03/2014          31/03/2014
any day in Feb          01/04/2014          30/04/2014
etc etc

Many thanks
0
Comment
Question by:andrewpiconnect
  • 2
4 Comments
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 250 total points
ID: 40019565
DueDate = DateSerial(Year([StartDate]), Month([StartDate]) + 2, 1)

SettlementDate = DateSerial(Year([StartDate]), Month([StartDate]) + 4, 0)
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 250 total points
ID: 40019790
SettlementDate exceeds by a month

should be
SettlementDate = DateSerial(Year([StartDate]), Month([StartDate]) + 3, 0)
0
 

Author Closing Comment

by:andrewpiconnect
ID: 40019884
Thanks Guys....works a treat
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40020032
Thanks for catching that Rey, I hate dd/mm/yy format!
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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…

828 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