Concatenated date range (2007)

[Caesar3]

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.)

[tgw7078]

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.

[Caesar3]

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.

[tgw7078]

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:

Option Compare Database
Option Explicit

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