Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2005
    Posts
    5

    Date Format


    I have a database that tracks requirements and when they where due. I've got a formula setup that shows whether a requirement was met (Delivered) or is late (Overdue). The customer wants to see how long the requirement is overdue. I've created an expression using DateDiff that shows how long the requirement is overdue based on today's date (ExprDate2Deliquent: DateDiff("d",[Need Date],Date())). The problem is I can only format in years or months or days.

    For example, if an item is 1072 days overdue, I can either show it's 1072 days overdue or 3 years overdue or 35 months overdue. How can I write an expression that will display. "X" years, "Y" months and "Z" days overdue?

  2. #2
    Join Date
    Dec 2005
    Location
    Wilmington, DE - USA
    Posts
    275
    Try adding this function in a regular VBA module (not a form, report, or class module):



    Code:
    Option Explicit
    
    Function YearsMonthsDays(Date1 As Date, Date2 As Date, Optional ShowAll As Boolean = True)
        
        Dim TestYear As Long, TestMonth As Long, TestDay As Long
        
        If Date1 > Date2 Then
            YearsMonthsDays = ""
            Exit Function
        End If
        
        If Year(Date2) > Year(Date1) Then
            If Month(Date2) = Month(Date1) Then
                If Day(Date2) >= Day(Date1) Then
                    TestYear = DateDiff("yyyy", Date1, Date2)
                Else
                    TestYear = DateDiff("yyyy", Date1, Date2) - 1
                End If
            ElseIf Month(Date2) > Month(Date1) Then
                TestYear = DateDiff("yyyy", Date1, Date2)
            Else
                TestYear = DateDiff("yyyy", Date1, Date2) - 1
            End If
        Else
            TestYear = 0
        End If
        
        TestMonth = (DateDiff("m", DateSerial(Year(Date1), Month(Date1), 1), DateSerial(Year(Date2), _
            Month(Date2), 1)) + IIf(Day(Date2) >= Day(Date1), 0, -1)) Mod 12
        
        If Day(Date2) >= Day(Date1) Then
            TestDay = Day(Date2) - Day(Date1)
        Else
            TestDay = DateDiff("d", DateSerial(Year(Date2), Month(Date2) - 1, Day(Date1)), Date2)
        End If
        
        If ShowAll Or TestYear >= 1 Then
            YearsMonthsDays = TestYear & IIf(TestYear = 1, " year, ", " years, ") & TestMonth & _
                IIf(TestMonth = 1, " month, ", " months, ") & TestDay & IIf(TestDay = 1, " day", " days")
        Else
            If TestMonth >= 1 Then
                YearsMonthsDays = TestMonth & IIf(TestMonth = 1, " month, ", " months, ") & _
                    TestDay & IIf(TestDay = 1, " day", " days")
            Else
                YearsMonthsDays = TestDay & IIf(TestDay = 1, " day", " days")
            End If
        End If
            
    End Function

    The function ignores the time portions of Date1 and Date2.

    The function returns a string "X years, Y months, Z days". ShowAll indicates whether or not you get the year portion if less than a year has passed, or the month portion if less than a month has passed. (If at least one year has passed, you always get at least "0 months" as part of the return string.)

    If Date1 is later than Date2 the function returns an empty string.

    Once you add the function to your project, it will be available in your other code, queries, forms, and reports:

    YearsMonthsDays([Need Date], Now())

  3. #3
    Join Date
    Dec 2005
    Posts
    5
    Thanks for the code. I've created a standard module using your code but I don't know how I add it to the form. How do I go about doing that and do I need to create a record in my query to store the data?

  4. #4
    Join Date
    Dec 2005
    Location
    Wilmington, DE - USA
    Posts
    275
    Use it in the ControlSource for an unbound textbox.

  5. #5
    Join Date
    Dec 2005
    Posts
    5
    I created an unbounded text box and put the name of the module in the Control Source and nothing's happening. I get the standard #Name? error.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Format / Autoformat / Sandstone Help.
    By caljohn527 in forum Database Design
    Replies: 0
    Last Post: 10-30-2008, 05:51 PM
  2. Text Box Format
    By Scott in forum Forms
    Replies: 0
    Last Post: 01-09-2008, 11:13 PM
  3. Date Format
    By wasim_sono in forum Forms
    Replies: 2
    Last Post: 08-15-2006, 01:20 AM
  4. Replies: 0
    Last Post: 07-24-2006, 06:31 AM
  5. Conditional Format Query
    By Schwagr in forum Queries
    Replies: 3
    Last Post: 03-20-2006, 02:39 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums