Date function, need to return Days/Weeks/Months from a given two dates...

[rtiddy]

Guys,

I urgently need to create a date function in VBA/Access that will take in two different date params and will return a string value like this...

"1 Months, 2 Weeks, 3 Days"

This will need return the time difference between the two date ranges in the above format...

If any of you guys could give some assistance, that would be great!

Rob.

[twinnyfo]

Have you tried any code on this yet? Plesae show us what you have to start and I would be glad to assist....

[twinnyfo]

You could try this:

Option Compare Database
Option Explicit
 
Private Sub cmdCheckDate_Click()
On Error GoTo EH
    If Not (IsNull(Me.txtFromDate) Or IsNull(Me.txtToDate)) And _
        Me.txtFromDate < Me.txtToDate Then
        Dim strDifference As String
        Dim YearFrom As Integer
        Dim YearTo As Integer
        Dim MonthFrom As Integer
        Dim MonthTo As Integer
        Dim WeekFrom As Integer
        Dim WeekTo As Integer
        Dim DayFrom As Integer
        Dim DayTo As Integer
        Dim YearDifference As Integer
        Dim MonthDifference As Integer
        Dim WeekDifference As Integer
        Dim DayDifference As Integer
        Dim dtTemp As Date
        YearFrom = Year(Me.txtFromDate)
        YearTo = Year(Me.txtToDate)
        MonthFrom = Month(Me.txtFromDate)
        MonthTo = Month(Me.txtToDate)
        DayFrom = Day(Me.txtFromDate)
        DayTo = Day(Me.txtToDate)
 
        If YearFrom < YearTo Then
            If MonthFrom < MonthTo Then
                YearDifference = YearTo - YearFrom
            Else
                YearDifference = YearTo - YearFrom - 1
            End If
        Else
            strDifference = "0 years, "
        End If
        If MonthFrom < MonthTo Then
            If DayFrom <= DayTo Then
                MonthDifference = MonthTo - MonthFrom
            Else
                MonthDifference = MonthTo - MonthFrom - 1
            End If
        Else
            If YearFrom < YearTo Then
                If DayFrom <= DayTo Then
                    MonthDifference = MonthTo - MonthFrom + 12
                Else
                    MonthDifference = MonthTo - MonthFrom + 11
                End If
            Else
                MonthDifference = 0
            End If
        End If
        dtTemp = DateAdd("yyyy", -YearDifference, Me.txtToDate)
        dtTemp = DateAdd("m", -MonthDifference, dtTemp)
        DayDifference = dtTemp - Me.txtFromDate
        Select Case DayDifference
            Case Is < 7
                WeekDifference = 0
            Case 7 To 13
                WeekDifference = 1
                DayDifference = DayDifference - 7
            Case 14 To 20
                WeekDifference = 2
                DayDifference = DayDifference - 14
            Case 21 To 27
                WeekDifference = 3
                DayDifference = DayDifference - 21
            Case Is >= 28
                WeekDifference = 4
                DayDifference = DayDifference - 28
        End Select
        strDifference = YearDifference & " year" & _
            IIf((YearDifference > 1 Or YearDifference = 0), "s, ", ", ") & _
            MonthDifference & " month" & _
            IIf((MonthDifference > 1 Or MonthDifference = 0), "s, ", ", ") & _
            WeekDifference & " week" & _
            IIf((WeekDifference > 1 Or WeekDifference = 0), "s, ", ", ") & _
            DayDifference & " day" & _
            IIf((DayDifference > 1 Or DayDifference = 0), "s", "")
        Me.TxtDateDifference = strDifference
    End If
    Exit Sub
EH:
    MsgBox Err.Number & " " & Err.Description
    Exit Sub
End Sub
 

[zmbd]

@rtiddy
As asked please post your work.

This is almost eactly like a homework question my compsci prof had us do in FORTRAN some two decades ago.
I've got this lying around in an old text file and once you post your work I'll take the time to convert it to VBA, I suspect that it should only take 18 lines... counting the DIM and OPTION statements!

-z
PS:
You can also do this as a series of calculated fields in a query :)

-z