none
Combine date RRS feed

  • Question

  • Dear Experts,

    I have facing a problem to combine date. 

    I have a 3 fields  in table. E.g. :-

    DateFrom     DateTo            Type

    17-Aug-09   18-Aug-09         A

    19-Sep-09   21-Sep-09         C

    20-Oct-09    24-Oct-09          A

    ...

    How am i going to combine the date to a single field and update it to a table below:

    Type           Date

    A                17/8-18/8, 20/10-24/10 ...

    Any ideals? Do it in query or VBA Code ?

    Your reply is highly appreciated. Thanks.

    Regards,

    DAnny

    Thursday, July 21, 2011 3:59 AM

Answers

  • Hi Danny
     Danny Gan wrote:

    I have a 3 fields in table. E.g. :-
    DateFrom DateTo Type
    17-Aug-09 18-Aug-09 A
    19-Sep-09 21-Sep-09 C
    20-Oct-09 24-Oct-09 A
    ...

    How am i going to combine the date to a single field and update it to a
    table below:
    Type Date
    A 17/8-18/8, 20/10-24/10 ...

    Do I understand correctly that you would like to have all DateFrom of all
    Records concatenated within a single field? And then all Records of Type C?

    If so, you have to concatenate all dateranges to a single output field. This
    can't be done in SQL directly, you will have to use a VBA-function that is
    doing this for you.

    This could look like this (untested)

    Public Function concatDateRanges(RecordType As String) As Variant
     Dim db As Database
     Dim rs As Recordset
     Dim varR As Variant
     varR = Null
     Set db = CurrentDB
     Set rs = db.OpenRecordset("SELECT DateFrom, DateTo " & _
       "FROM YourTable WHERE [Type]='" & RecordType & "'" & _
       " ORDER BY FromDate", dbOpenSnapshot
     While Not rs.EOF
       varR = varR + ", " & Format(DateFrom, "DD\/M") & "-" & _
       Format(DateTo, "DD\/M")
       rs.MoveMext
     Wend
     concatDateRanges = varR
     rs.Close
     Set rs = Nothing
     Set db = Nothing
    End Function

    Now you can query your database as follows:

    SELECT [Type], concatDateRanges([Type]) AS [Date]
     FROM YourTable
     ORDER BY [Type]
     GROUP BY [Type]

    HTH
    Henry

    • Marked as answer by Danny Gan Friday, July 22, 2011 9:53 AM
    Friday, July 22, 2011 8:25 AM

All replies

  • Try this query in SQL mode and replace "Table1" with the actual table name.

     

    SELECT Table1.DateFrom, Table1.DateTo, [datefrom] & " - " & [dateto] AS Expr1
    FROM Table1;

     

     


    - Abel
    Thursday, July 21, 2011 9:22 AM
  • Hi Danny
     Danny Gan wrote:

    I have a 3 fields in table. E.g. :-
    DateFrom DateTo Type
    17-Aug-09 18-Aug-09 A
    19-Sep-09 21-Sep-09 C
    20-Oct-09 24-Oct-09 A
    ...

    How am i going to combine the date to a single field and update it to a
    table below:
    Type Date
    A 17/8-18/8, 20/10-24/10 ...

    Do I understand correctly that you would like to have all DateFrom of all
    Records concatenated within a single field? And then all Records of Type C?

    If so, you have to concatenate all dateranges to a single output field. This
    can't be done in SQL directly, you will have to use a VBA-function that is
    doing this for you.

    This could look like this (untested)

    Public Function concatDateRanges(RecordType As String) As Variant
     Dim db As Database
     Dim rs As Recordset
     Dim varR As Variant
     varR = Null
     Set db = CurrentDB
     Set rs = db.OpenRecordset("SELECT DateFrom, DateTo " & _
       "FROM YourTable WHERE [Type]='" & RecordType & "'" & _
       " ORDER BY FromDate", dbOpenSnapshot
     While Not rs.EOF
       varR = varR + ", " & Format(DateFrom, "DD\/M") & "-" & _
       Format(DateTo, "DD\/M")
       rs.MoveMext
     Wend
     concatDateRanges = varR
     rs.Close
     Set rs = Nothing
     Set db = Nothing
    End Function

    Now you can query your database as follows:

    SELECT [Type], concatDateRanges([Type]) AS [Date]
     FROM YourTable
     ORDER BY [Type]
     GROUP BY [Type]

    HTH
    Henry

    • Marked as answer by Danny Gan Friday, July 22, 2011 9:53 AM
    Friday, July 22, 2011 8:25 AM