Results 1 to 2 of 2
  1. #1
    maloykundu is offline Novice
    Windows XP Access 2007
    Join Date
    Nov 2010
    Posts
    1

    Post Query to autofill date and calculate duration in days months and years

    There is a database table name tblTenure with following fields :
    IDNo (AutoNumber) Primary Key
    PersonID (Text)
    PersonName (Text)
    Place (Text)
    FromDate (Date)

    I want to create a query based on the above tblTenure, which will display the following data with two additional field named ToDate and Tenure :-

    IDNo.
    PersonID
    PersonName
    Place
    FromDate
    ToDate
    Tenure

    The ToDate in the query should automatically be filled up by taking the FromDate of the next row of the person with same PersonID. The FromDate should be less by one day. Then the Tenure field should calculate the difference between FromDate and ToDate in Years, Months and Days. It may please be noted that last place of posting should be left blank as shown in the example below :-

    Example : tblTenure

    IDNo
    PersonID
    PersonName
    Place
    FromDate
    1
    JN
    Jhon
    Mumbai
    1/1/1987
    2
    JK
    Jack
    Delhi
    7/8/1985
    3
    JN
    Jhon
    Kolkata
    7/8/1989
    4
    JL


    Jill
    Pune
    8/1/1990
    5
    JN
    Jhon
    Agra
    5/10/1990
    6
    JL
    Jill
    Kolkata
    10/10/1992
    7
    JN
    Jhon
    Kolkata
    11/11/1995


    The query should display the above data in following fashion :-

    IDNo
    PersonID
    PersonName
    Place
    FromDate
    ToDate
    Tenure
    1
    JN
    Jhon
    Mumbai
    1/1/1987
    6/8/1989
    2 Years 7 Months 5 Days
    3
    JN
    Jhon
    Kolkata
    7/8/1989
    4/10/1990
    1 Year 1 Month 27 Days
    5
    JN
    Jhon
    Agra
    5/10/1990
    10/11/1995
    5 Years 1 Month 5 Days
    7
    JN
    Jhon
    Kolkata
    11/11/1995


    2
    JK
    Jack
    Delhi
    7/8/1985


    4
    JL
    Jill
    Pune
    8/1/1990
    9/10/1992
    2 Years 9 Months 1 Day
    6
    JL
    Jill
    Kolkata
    10/10/1992




  2. #2
    ConneXionLost's Avatar
    ConneXionLost is offline Simulacrum
    Windows XP Access 2003
    Join Date
    Jan 2010
    Location
    Victoria, Canada
    Posts
    291
    This should help for the Tenure:

    Code:
    Public Function Diff2Dates(Interval As String, Date1 As Variant, Date2 As Variant, _
    Optional ShowZero As Boolean = False) As Variant
    
    'Author:    ) Copyright 2001 Pacific Database Pty Limited
    '           Graham R Seach MCP MVP gseach@pacificdb.com.au
    '           Phone: +61 2 9872 9594  Fax: +61 2 9872 9593
    '           This code is freeware. Enjoy...
    '           (*) Amendments suggested by Douglas J. Steele MVP
    '
    'Description:   This function calculates the number of years,
    '               months, days, hours, minutes and seconds between
    '               two dates, as elapsed time.
    '
    'Inputs:    Interval:   Intervals to be displayed (as a string).  Use:
    '           y = years
    '           m = months
    '           d = days
    '           h = hours
    '           n = minutes
    '           s = seconds
    '           Date1:      The lower date (see below)
    '           Date2:      The higher date (see below)
    '           ShowZero:   Boolean to select showing zero elements
    '
    'Outputs:   On error: Null
    '           On no error: Variant containing the number of years,
    '               months, days, hours, minutes & seconds between
    '               the two dates, depending on the display interval
    '               selected.
    '           If Date1 is greater than Date2, the result will
    '               be a negative value.
    '           The function compensates for the lack of any intervals
    '               not listed. For example, if Interval lists "m", but
    '               not "y", the function adds the value of the year
    '               component to the month component.
    '           If ShowZero is True, and an output element is zero, it
    '               is displayed. However, if ShowZero is False or
    '               omitted, no zero-value elements are displayed.
    '               For example, with ShowZero = False, Interval = "ym",
    '               elements = 0 & 1 respectively, the output string
    '               will be "1 month" - not "0 years 1 month".
    '
    'For example:
    '
    '?Diff2Dates("y", #06/01/1998#, #06/26/2002#)
    '4 years
    '?Diff2Dates("ymd", #06/01/1998#, #06/26/2002#)
    '4 years 25 days
    '?Diff2Dates("ymd", #06/01/1998#, #06/26/2002#, True)
    '4 years 0 months 25 days
    '?Diff2Dates("d", #06/01/1998#, #06/26/2002#)
    '1486 days
    '
    '?Diff2Dates("h", #01/25/2002 01:23:01#, #01/26/2002 20:10:34#)
    '42 hours
    '?Diff2Dates("hns", #01/25/2002 01:23:01#, #01/26/2002 20:10:34#)
    '42 hours 47 minutes 33 seconds
    '?Diff2Dates("dhns", #01/25/2002 01:23:01#, #01/26/2002 20:10:34#)
    '1 day 18 hours 47 minutes 33 seconds
    '
    '?Diff2Dates("ymd",#12/31/1999#,#1/1/2000#)
    '1 day
    '?Diff2Dates("ymd",#1/1/2000#,#12/31/1999#)
    '-1 day
    '?Diff2Dates("ymd",#1/1/2000#,#1/2/2000#)
    '1 day
    '
    
    
    On Error GoTo Err_Diff2Dates
    
       Dim booCalcYears As Boolean
       Dim booCalcMonths As Boolean
       Dim booCalcDays As Boolean
       Dim booCalcHours As Boolean
       Dim booCalcMinutes As Boolean
       Dim booCalcSeconds As Boolean
       Dim booSwapped As Boolean
       Dim dtTemp As Date
       Dim intCounter As Integer
       Dim lngDiffYears As Long
       Dim lngDiffMonths As Long
       Dim lngDiffDays As Long
       Dim lngDiffHours As Long
       Dim lngDiffMinutes As Long
       Dim lngDiffSeconds As Long
       Dim varTemp As Variant
    
       Const INTERVALS As String = "dmyhns"
    
    'Check that Interval contains only valid characters
       Interval = LCase$(Interval)
       For intCounter = 1 To Len(Interval)
          If InStr(1, INTERVALS, Mid$(Interval, intCounter, 1)) = 0 Then
             Exit Function
          End If
       Next intCounter
    
    'Check that valid dates have been entered
       If IsNull(Date1) Then Exit Function
       If IsNull(Date2) Then Exit Function
       If Not (IsDate(Date1)) Then Exit Function
       If Not (IsDate(Date2)) Then Exit Function
    
    'If necessary, swap the dates, to ensure that
    'Date1 is lower than Date2
       If Date1 > Date2 Then
          dtTemp = Date1
          Date1 = Date2
          Date2 = dtTemp
          booSwapped = True
       End If
    
       Diff2Dates = Null
       varTemp = Null
    
    'What intervals are supplied
       booCalcYears = (InStr(1, Interval, "y") > 0)
       booCalcMonths = (InStr(1, Interval, "m") > 0)
       booCalcDays = (InStr(1, Interval, "d") > 0)
       booCalcHours = (InStr(1, Interval, "h") > 0)
       booCalcMinutes = (InStr(1, Interval, "n") > 0)
       booCalcSeconds = (InStr(1, Interval, "s") > 0)
    
    'Get the cumulative differences
       If booCalcYears Then
          lngDiffYears = Abs(DateDiff("yyyy", Date1, Date2)) - _
                  IIf(Format$(Date1, "mmddhhnnss") <= Format$(Date2, "mmddhhnnss"), 0, 1)
          Date1 = DateAdd("yyyy", lngDiffYears, Date1)
       End If
    
       If booCalcMonths Then
          lngDiffMonths = Abs(DateDiff("m", Date1, Date2)) - _
                  IIf(Format$(Date1, "ddhhnnss") <= Format$(Date2, "ddhhnnss"), 0, 1)
          Date1 = DateAdd("m", lngDiffMonths, Date1)
       End If
    
       If booCalcDays Then
          lngDiffDays = Abs(DateDiff("d", Date1, Date2)) - _
                  IIf(Format$(Date1, "hhnnss") <= Format$(Date2, "hhnnss"), 0, 1)
          Date1 = DateAdd("d", lngDiffDays, Date1)
       End If
    
       If booCalcHours Then
          lngDiffHours = Abs(DateDiff("h", Date1, Date2)) - _
                  IIf(Format$(Date1, "nnss") <= Format$(Date2, "nnss"), 0, 1)
          Date1 = DateAdd("h", lngDiffHours, Date1)
       End If
    
       If booCalcMinutes Then
          lngDiffMinutes = Abs(DateDiff("n", Date1, Date2)) - _
                  IIf(Format$(Date1, "ss") <= Format$(Date2, "ss"), 0, 1)
          Date1 = DateAdd("n", lngDiffMinutes, Date1)
       End If
    
       If booCalcSeconds Then
          lngDiffSeconds = Abs(DateDiff("s", Date1, Date2))
          Date1 = DateAdd("s", lngDiffSeconds, Date1)
       End If
    
       If booCalcYears And (lngDiffYears > 0 Or ShowZero) Then
          varTemp = lngDiffYears & IIf(lngDiffYears <> 1, " years", " year")
       End If
    
       If booCalcMonths And (lngDiffMonths > 0 Or ShowZero) Then
          If booCalcMonths Then
             varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
                       lngDiffMonths & IIf(lngDiffMonths <> 1, " months", " month")
          End If
       End If
    
       If booCalcDays And (lngDiffDays > 0 Or ShowZero) Then
          If booCalcDays Then
             varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
                       lngDiffDays & IIf(lngDiffDays <> 1, " days", " day")
          End If
       End If
    
       If booCalcHours And (lngDiffHours > 0 Or ShowZero) Then
          If booCalcHours Then
             varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
                       lngDiffHours & IIf(lngDiffHours <> 1, " hours", " hour")
          End If
       End If
    
       If booCalcMinutes And (lngDiffMinutes > 0 Or ShowZero) Then
          If booCalcMinutes Then
             varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
                       lngDiffMinutes & IIf(lngDiffMinutes <> 1, " minutes", " minute")
          End If
       End If
    
       If booCalcSeconds And (lngDiffSeconds > 0 Or ShowZero) Then
          If booCalcSeconds Then
             varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
                       lngDiffSeconds & IIf(lngDiffSeconds <> 1, " seconds", " second")
          End If
       End If
    
       If booSwapped Then
          varTemp = "-" & varTemp
       End If
    
       Diff2Dates = Trim$(varTemp)
    
    End_Diff2Dates:
       Exit Function
    
    Err_Diff2Dates:
       Resume End_Diff2Dates
    
    End Function
    Cheers,

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

Similar Threads

  1. Replies: 11
    Last Post: 08-04-2010, 04:26 PM
  2. calculate no. of working days
    By JOSE LUIS in forum Access
    Replies: 1
    Last Post: 02-01-2010, 03:55 PM
  3. How to calculate duration in hours & minutes
    By joypanattil in forum Access
    Replies: 0
    Last Post: 11-25-2009, 04:49 AM
  4. Replies: 2
    Last Post: 07-31-2009, 06:56 AM
  5. query date that is five years older than today
    By cpsummer in forum Queries
    Replies: 2
    Last Post: 09-26-2007, 02:31 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