Results 1 to 4 of 4
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Concatenated date range (2007)

    A field in a query concatenates the dates for a training session from two fields: SessionStart and SessionEnd. Both fields are formatted dd/mm/yyyy. Thus, in the query, the date range displays "6/18/2012-6/22/2012."

    This calculated field will populate a mail-merge field on a certificate where I need it to display "18-22 June 2012." How do I do that? (FWIW: The certificate is built in MS-Publisher.)

  2. #2
    Star Lounger tgw7078's Avatar
    Join Date
    Jul 2010
    Location
    Seattle, WA., USA
    Posts
    90
    Thanks
    1
    Thanked 12 Times in 12 Posts
    Hi Ceasar,

    How would the final result appear if the session start & end dates spanned two months, for example 07/30/2012 to 08/03/2012?

    If you can *always* guarantee that both the session start and end dates will be in the same month, then you could create a fairly simple expression to return the desired result. However, I question if one could really rely on such a guarantee...

    If spanning > 1 month is a possibility, then you will likely need to write a custom VBA procedure. A bit more difficult, but certainly do-able.

    More information needed.
    Tom Wickerath
    Microsoft Access MVP
    4/1/2006 - 3/31/2012

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post
    Thanks for the reply, Tom. It so happens that all the dates for this year (fiscal year, that is, which ends on September 30) fall within a single month. But you're right: Fat chance it'll always be that way. I'll go back and re-think this.

  4. #4
    Star Lounger tgw7078's Avatar
    Join Date
    Jul 2010
    Location
    Seattle, WA., USA
    Posts
    90
    Thanks
    1
    Thanked 12 Times in 12 Posts
    Hi Again -

    I decided to try a Sunday morning challenge to try to help you more. If the SessionStart and SessionEnd dates fall within a single month, then you can use a query--no VBA code required. For this example, I named the table "tblTrainingSessions". The fields are named "SessionStart" and "SessionEnd", respectively, and are both Date/Time data type formatted as you indicated (dd/mm/yyyy). Here is some sample data that I entered into the table. TrainingID is simply an Autonumber primary key:

    Data.gif


    The test data includes your original dates, a set of dates that spans two months, your original dates entered backwards, and two records with nulls.

    Query1 SQL Statement
    SELECT SessionStart, SessionEnd, Day([SessionStart]) & "-" & Day([SessionEnd]) & " " & Format([SessionStart],"mmmm yyyy") AS TrainingSession
    FROM tblTrainingSessions;

    Query1 Result with above test data:
    Q1.gif

    As you can see, the last four results have an error. You can eliminate results # 4 and 5 by adding the appropriate criteria to the query, but that still leaves the 2nd and 3rd results:

    SELECT SessionStart, SessionEnd, Day([SessionStart]) & "-" & Day([SessionEnd]) & " " & Format([SessionStart],"mmmm yyyy") AS TrainingSession
    FROM tblTrainingSessions
    WHERE SessionStart Is Not Null AND SessionEnd Is Not Null;

    Query that calls VBA procedure
    Create a new module and paste the following code into this module. Make sure that your new module has "Option Explicit" as the second line:
    Code:
    Option Compare Database
    Option Explicit
    Code:
    Public Function DetermineRange(StartDate As Variant, _
                                   EndDate As Variant) As String
                                   
    On Error GoTo ProcError
    Dim intDays As Integer
       If IsNull(StartDate) = True Or IsNull(EndDate) = True Then
          DetermineRange = ""  '<--Return a zero length string.
       Else
          intDays = DateDiff("d", [StartDate], [EndDate]) + 1
          
          'Check for "reasonableness" of number of days in range.
          If intDays < 3 Or intDays > 5 Then
             DetermineRange = "Check for correct date entries."
          Else
             'Check to see if both dates are in the same month.
             If Month([StartDate]) = Month([EndDate]) Then
                DetermineRange = Day([StartDate]) & "-" & Day([EndDate]) _
                                     & " " & Format([StartDate], "mmmm yyyy")
             Else
               'Session spans two months (for example 30-July to 03-August)
                'Note: Add an extra "m" to each format to get the full month names
                DetermineRange = Day([StartDate]) & " " & Format([StartDate], "mmm") _
                               & " to " & Day([EndDate]) & " " & Format([EndDate], "mmm yyyy")
             End If
             
          End If
          
       End If
    ExitProc:
       Exit Function
    ProcError:
       DetermineRange = "Error " & Err.Number & ": " & Err.Description
       Resume Next
    End Function
    



    SQL Statement for Query that calls VBA procedure:
    SELECT SessionStart, SessionEnd, DetermineRange([SessionStart],[SessionEnd]) AS TrainingSession
    FROM tblTrainingSessions;

    Here is the result of this query:
    Q2.gif
    Last edited by tgw7078; 2012-06-24 at 15:53.
    Tom Wickerath
    Microsoft Access MVP
    4/1/2006 - 3/31/2012

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •