Date add 1

[wattyl]

I Have three boxes on a form
1. Number of years (not constant)
2, Start Date ("dd"mmm"yy&quot
3. End Date ("dd"mmm"yy&quot
The end date should = Start date + box1 The figure in box 1 is always different for each record.ie 12 15 16 20
Can anyone help with the formula for my query to automatically calculate the end date

 

[shannonp]

Try This

'***Code Begins

Private Sub Number_of_Years_AfterUpdate()
Dim DayDiff As Integer

DayDiff = DateDiff("d", [Start Date], Format(Day([Start Date]) & "/" & Month([Start Date]) & "/" & [Number of Years], "dd/mm/yy&quot)
[End Date] = [Start Date] + [DayDiff]
MsgBox [End Date]
End Sub

'***End Of Code

Hope it helps

 

[shannonp]

Oops - try this one. As long as 'Number of Years' does not exceed 27 you'll be fine with this code...

'***Code Begins

Private Sub Number_of_Years_AfterUpdate()
Dim DayDiff As Integer

DayDiff = DateDiff("d", [Start Date], Format(Day([Start Date]) & "/" & Month([Start Date]) & "/" & [Number of Years] + Year([Start Date]), "dd/mm/yy&quot)
MsgBox [DayDiff]
[End Date] = [Start Date] + [DayDiff]
MsgBox [End Date]
End Sub

'***End Of Code

 

[wattyl]

Having problem running this code.
Is there any way I can do this using a query and placing the code in the "Field Box" of the query

 

[shannonp]

Try using this in your query (make sure you have included [Number of Years] and [Start Date] in your query.

If you wanted to store these values to a table then use an update query (to update 'Finish Date') instead of a standard select query.

---
Finish Date: Format(Day([Start Date]) & "/" & Month([Start Date]) & "/" & Year([Start Date])+[Number Of Years],"Medium Date&quot
---

Cheers

 

[KenReay]

Hi

Surely your code should be

EndDate = DateAdd("y",NoOfYears,StartDate)

This code needs to be executed in the after update event of StartDate and NoOfYears, if it is user input, if it is comming from a recordsource of a bound form, put it in the oncurrent event of the form Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[wattyl]

Ken,
I have a query and tried to use
"DateAdd("y",NoOfYears,[StartDate])" as you suggested.
I used -3 as the "NumberOfYears" to subtract but this is not subtracting the three years , instead it only gives a subtraction of 3 days on my end date from start date.

The start date is already calculated from an initial date in the same query.
Any suggestions to help appreciated.
Watty

 

[raskew]

Check the help file for the DateAdd Function.

To respond correctly, your function should read:

EndDate = DateAdd("yyyy",NoOfYears,StartDate)

 

[KenReay]

Hi

Yes, the Help option is a wonderful although under used tool! Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK