DateDiff

[cer5yc]

Can someone please tell me how to get the following to return the # of months and # of days, in addition to the # of years. Thanks!

Private Sub ContractTerm_GotFocus()
If Not IsNull(txtContractEffectiveDate) And Not IsNull(txtContractTermDate) Then
Me.ContractTerm.Value = DateDiff("yyyy", [txtContractEffectiveDate], [txtContractTermDate])
Else
End If
End Sub

 

[SkipVought]

Hi,

format([txtContractEffectiveDate], [txtContractTermDate]),"y m d")

yrs mos days

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[SkipVought]

Forgot the [red]MINUS[/red] sign...

format([txtContractEffectiveDate][red][b]-[/b][/red][txtContractTermDate]),"y m d")

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[cer5yc]

Well that at least gave me a value! But here's what I have now:

In the txtContractEffectiveDate text box I have a date of 10/11/2007

In the txtContractTermDate text box I have a date of 1/19/2008


The value I am getting returned is 1899 9 21

Here's what I have written:
Private Sub ContractTerm_GotFocus()
If Not IsNull(txtContractEffectiveDate) And Not IsNull(txtContractTermDate) Then
Me.ContractTerm.Value = Format(([txtContractEffectiveDate] - [txtContractTermDate]), "yyyy m d")
Else
End If
End Sub

Any thoughts?

 

[SkipVought]

You're gettin a negative value

format(abs([txtContractEffectiveDate]-[txtContractTermDate])),"y m d")

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[cer5yc]

I switched it around and now I'm getting a value of
1900 4 9. Still not right....any other ideas?

 

[SkipVought]

You're gettin a negative value

CInt(Format(Abs([txtContractEffectiveDate] - [txtContractTermDate]), "yyyy")) - 1900 & " " & _
Format(Abs([txtContractEffectiveDate] - [txtContractTermDate]), "m d")

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[cer5yc]

Getting closer Now I have a Contract Effective Date of 10/11/07 and a Contract Term Date of 10/10/08 and the value I get returned is 0 12 30

Private Sub ContractTerm_GotFocus()
If Not IsNull(txtContractEffectiveDate) And Not IsNull(txtContractTermDate) Then
Me.ContractTerm.Value = CInt(Format(Abs([txtContractEffectiveDate] - [txtContractTermDate]), "yyyy")) - 1900 & " " & _
Format(Abs([txtContractEffectiveDate] - [txtContractTermDate]), "m d")
End If
End Sub


I need it to return 1 year 0 months 0 days

 

[cer5yc]

Is there no way to get the DateDiff function to return the year, month, day because it gives me the correct value, it just gives me the year, the month, or the days and I need all three

 

[SkipVought]

I dont think that my approch is really sound.

Where you're doing date calculations, you have to make some rules, because there is no standard value such as year or month.

So how many days is 1 year, or 1 month? Depends on your DEFINITION.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[lameid]

You might consider using datediff to get the year.

Keep that value and use daeadd to add it to the smaller value.

Then Use datediff to ge the month of the new values.

Then add the month to the lower of the 2 values...

I think you see where I am going. I think that is what you will be after.

 

[missinglinq]

The problem, as Skip alluded to in his last post, is that all years don't have 365 days in them, and months can have 28, 29, 30 or 31 days! So any conversion I've every seen is going to be slightly inaccurate. With that in mind, from the Objects Dialog box goto Modules. Click on New and paste in this code:

Function D2YMD(No As Integer) As Variant
Dim Y As Long
Dim M As Long
Dim D As Long

Y = Int(No / 365)
M = Int((No - (Int(No / 365) * 365)) / 30)
D = No - ((Y * 365) + (M * 30))
 
D2YMD = Y & " years " & M & " months " & D & " days"

End Function

When Access asks if you want to save the module answer Yes and name it DaysConversions.

I apologize; I have a function where you simply enter two dates and the function does it all, but I can't find the $%^*#$% thing and don't have time to re-create it! So you'll need to use the DateDiff to get the number of days difference then plug it into the function:

Dim Days as Integer
Days = DateDiff("d",Me.txtContractEffectiveDate,Me.txtContractTermDate)

Me.ContractTerm.Value = D2YMD(Days)

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!