Autofill Field Date3 Based on Fields Date1 and Date2

[777bonzai]

Hi,

hope someone could help.
On a form, I have three date fields Date1, Date2 and Date3 and format of all three fields is shortdate. I want field Date3 to be auto-poluate by this formula (Date1 - Date2) +1 after user enter Date1 and Date2. I built this VAB code on the AfterEvent of Date2 field.

Private Sub Date2_AfterUpdate()
If Not IsNull(Me![Date2]) And IsDate(Me![Date2]) And Not IsNull(Me![Date1]) And IsDate(Me![Date1]) Then
Me.Date3 = (([Date2]-[Date1])+1)
End If
End Sub

I got the result of a date but not the correct date as 12/29/1899. any idea why?
thanks
777bonzai

[Rabbit]

First and foremost, you should avoid storing calculated values. You can just run the calculation in real time when needed using an expression or a view.

Also, I don't know what you're expecting from your calculation because you haven't told us what the correct result is supposed to be.

But here's the million dollar question, why do you expect the subtraction of 2 dates to result in another date?

If you subtract Christmas Day from New Year's Day, why do you expect a date to come out of that? And what date do you expect to come out? Logically speaking, I expect a time interval to come out, not a date. If I subtract 2 dates, I expect the number of days that separate the two dates as a result.

[777bonzai]

Rabbit. Thanks for your response. Basically, for example, if user enter Date1 as 01/05/2011 and Date2 as 01/02/2011, Date3 should be auto populated as a date of 01/04/2011
thanks
777bonzai.

[Rabbit]

And why's that? The result makes no sense. If I subtract 01/05/2011 and 01/02/2011, the answer I expect is 3 days. Not a date.

You need to explain in more detail how you're making that leap of logic. What happens if you subtract 07/30/2011 and 01/02/2011?

Right now, you're doing math that only makes sense in your head, you need to explain your version of math.

[zmbd]

777bonzai,

+ Are the controls [Date1] and [Date2] "unbound", if not, then I concur with Rabbit's comment about storing calculated values. There are only a few, rare, exceptions to this best practice.

+ Why 1/4/2011 instead of 1/3/2011? Say you had day1=1/1/2015 and day2=12/30/2015 would you want the return of 12/29/2015?

+ You also have no check for Day1>Day2 so would you desire that the in the case of day1=12/30/2015 and day2=1/1/2015 return some value (say 01/01/2015 or 01/02/2015, or 12/31/2014) or instead return #01/01/1900#?

[NeoPa]

Rabbit:
And why's that? The result makes no sense. If I subtract 01/05/2011 and 01/02/2011, the answer I expect is 3 days. Not a date.

I agree absolutely. Unless and until you can explain why you think it makes sense to try to express a date interval as a date, the question is impossible to answer. Sometimes people guess at what the question might mean if the person asking it had expressed it properly, but that's a really bad idea, even when they're right.

If you can rephrase the question for us, such that it makes sense, then you have the ear of three very experienced Access developers here waiting to help you.

[777bonzai]

I was able to figure it out. Thanks everyone!