Solved

access 2010 vba calculate dates

Posted on 2014-04-24
4
647 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
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.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

763 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