Year and Month and data search

[spooshee]

Hello Everyone! Im new in VB-Access development and I have a project in school about database programming. The program should compute and display the total number of years and month based on the system date, it should be displayed in a textbox. Here's an example:

Let say the system date = Feb 24, 2007 and the entry date = April 24, 2005

the display output should be:
1 year and 10 months


Also, about the search button, when the a string has been input, it will display all the records with the same string... Please Help! Thank you very much to all!

[Siddharth Rout]

Let say the system date = Feb 24, 2007 and the entry date = April 24, 2005

the display output should be:
1 year and 10 months

To find a difference between two dates, use the datediff fuction

[spooshee]

Hello and Thank you! Yes, I already used the datediff(), however, when I use it to return the year, it rounds up the year when it reached the 6th month, I need something that will break down the year and month if it is not exactly within the whole year. Please help, Thank you!

[Siddharth Rout]

You can use a series of DateDiff calls, but it gets a little complex, especially since DateDiff doesn't report years and months in a way that you might want.

Let me see if i can write the entire code for you...

[spooshee]

Thank you so much! Im still trying to figure this out... Please do!

[lintz]

I also added days

Dim dStartDate As Date
Dim dEndDate As Date
Dim iYears As Integer
Dim iMonths As Integer
Dim iDays As Integer
 
dStartDate = "24/04/2005"
dEndDate = "25/02/2007"
 
'first do the years
iYears = DateDiff("yyyy", dStartDate, dEndDate) - 1
 
    'then the months
    If iYears > 0 Then
    iMonths = DateDiff("m", DateAdd("yyyy", iYears, dStartDate), dEndDate)
    
    Else
    
    iMonths = DateDiff("m", dStartDate, dEndDate)
    
    End If
    
    
    'and now the days
    If iMonths > 0 Then
    dStartDate = DateAdd("yyyy", iYears, dStartDate)
    dStartDate = DateAdd("m", iMonths, dStartDate)
    
    iDays = DateDiff("d", dStartDate, dEndDate)
    
    Else
    
    iDays = DateDiff("d", dStartDate, dEndDate)
    
    End If

[spooshee]

Thank you very much! Im gonna try it right away as soon as i got home! really thank you so much! Would it be ok to ask an assistance from u from time to time, since Im a beginner in VB?

[Siddharth Rout]

@ Spooshe : That's a wonderful code given by Lintz

Here is another one... which will give you the result in Years, Months and Days....

Private Sub Command1_Click()
    'For example
    MsgBox Spooshe(#4/24/2005#, #2/24/2007#, True, True, True)
End Sub
 
Public Function Spooshe(StartDate As Date, EndDate As Date, Optional WithMonths As Boolean = False, _
    Optional WithDays As Boolean = False, Optional DisplayWithWords As Boolean = False) As Variant
    
    On Error GoTo Spooshe_ErrorHandler
    
    Dim iYears As Integer
    Dim iMonths As Integer
    Dim iDays As Integer
    Dim dTempDate As Date
    
    'Check that the dates are valid
    If Not (IsDate(StartDate)) Or Not (IsDate(EndDate)) Then
        DoCmd.Beep
        MsgBox "Invalid date.", vbOKOnly + vbInformation, "Invalid date"
        GoTo Spooshe_ErrorHandler
    End If
    
    'Check that StartDate < EndDate
    If StartDate > EndDate Then
        DoCmd.Beep
        MsgBox "EndDate must be greater than StartDate.", _
            vbOKOnly + vbInformation, "Invalid date position"
        GoTo Spooshe_ErrorHandler
    End If
    
    iYears = DateDiff("yyyy", StartDate, EndDate) - _
        IIf(DateAdd("yyyy", DateDiff("yyyy", StartDate, EndDate), StartDate) > EndDate, 1, 0)
    dTempDate = DateAdd("yyyy", iYears, StartDate)
    
    If WithMonths Then
        iMonths = DateDiff("m", dTempDate, EndDate) - _
            IIf(DateAdd("m", iMonths, DateAdd("yyyy", iYears, StartDate)) > EndDate, 1, 0)
        dTempDate = DateAdd("m", iMonths, dTempDate)
    End If
    
    If WithDays Then
        iDays = EndDate - dTempDate
    End If
    
    'Format the output
    If DisplayWithWords Then
        'Display the output in words
        Spooshe = IIf(iYears > 0, iYears & " year" & IIf(iYears <> 1, "s ", " "), "")
        Spooshe = Spooshe & IIf(WithMonths, iMonths & " month" & IIf(iMonths <> 1, "s ", " "), "")
        Spooshe = Trim(Spooshe & IIf(WithDays, iDays & " day" & IIf(iDays <> 1, "s", ""), ""))
    Else
        'Display the output in the format yy.mm.dd
        Spooshe = Trim(iYears & IIf(WithMonths, "." & Format(iMonths, "00"), "") _
            & IIf(WithDays, "." & Format(iDays, "00"), ""))
    End If
    
Exit_Spooshe:
    Exit Function
    
Spooshe_ErrorHandler:
    Spooshe = Null
    Resume Exit_Spooshe
End Function

Hope this helps....