Concatenated date range (2007)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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:

31280-Data

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:
31281-Q1

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:

[FONT font=Arial][COLOR=#000000]Public Function DetermineRange(StartDate As Variant, _
                               EndDate As Variant) As String
                               
On Error GoTo ProcError[/COLOR][/FONT]
[FONT font=Arial][COLOR=#000000]Dim intDays As Integer
   If IsNull(StartDate) = True Or IsNull(EndDate) = True Then
      DetermineRange = “”  ‘[/COLOR][COLOR=#006400]<–Return a zero length string.[/COLOR][COLOR=#000000]
   Else
      intDays = DateDiff("d", [StartDate], [EndDate]) + 1
      
[/COLOR][COLOR=#006400]      'Check for "reasonableness" of number of days in range.[/COLOR][COLOR=#000000]
      If intDays  5 Then
         DetermineRange = “Check for correct date entries.”
      Else
         [/COLOR][COLOR=#006400]’Check to see if both dates are in the same month.[/COLOR][COLOR=#000000]
         If Month([StartDate]) = Month([EndDate]) Then
            DetermineRange = Day([StartDate]) & “-” & Day([EndDate]) _
                                 & ” ” & Format([StartDate], “mmmm yyyy”)
         Else
       [/COLOR][COLOR=#006400]    ‘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[/COLOR][COLOR=#000000]
            DetermineRange = Day([StartDate]) & ” ” & Format([StartDate], “mmm”) _
                           & ” to ” & Day([EndDate]) & ” ” & Format([EndDate], “mmm yyyy”)
         End If
         
      End If
      
   End If[/COLOR][/FONT]
[FONT font=Arial][COLOR=#000000]ExitProc:
   Exit Function
ProcError:
   DetermineRange = “Error ” & Err.Number & “: ” & Err.Description
   Resume Next
End Function
[/COLOR][/FONT]

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:
31282-Q2

Reply | Quote