stubborn query- pls help..

[alidaanish]

hi!
following is a query that isnt quite doing me right. most of it works, except for the column - Session Duration -



Two types of outputs- either for a course that is only one day, or a course that is 2 or more days...
i'm trying to get the output to look like:

a)Mar. 1. 2000
b)Mar 2 - 4, 2004
c)May 30 - Apr 1, 2004

i realise that the formula can be a whole lot simpler, but i'd like to get it working first..ehe..
another thing- whilst counting days for the session duration- we include the day it starts...ie. course starts May16 and ends May 19..thats 4 days..


Thank you so much for your help in advance!
alidaanish
_________________________________________________________________
SELECT
[Course Schedule].SessionID, [Course Schedule].[Course ID] & ' - ' & [Course Start Date]

AS [Session], [Course Schedule].[Course ID], [List of Courses].[Course Title], [Course Schedule].[Course Start Date], IIf([Course Duration]=0.5 Or [Course Duration]=1.5

Or [Course Duration]=2.5,[Course Start Date]+[Course Duration]+0.5-1,[Course Start Date]+[Course Duration]-1)

AS [Course End Date], MonthName(Month([Course Start Date]),True)

AS [Start Month], MonthName(Month([Course End Date]),True)

AS [End Month], Day([Course Start Date])

AS [Start Day], Day([Course End Date])

AS [End Day], IIf(DateValue([Course Start Date])<DateValue([Course End Date]),IIf([Start Month]=[End Month],[Start Month] & " " & [Start Day] & " - " & [End Day] & ", " & Year([Course End Date]),[Start Month] & " " & [Start Day] & " - " & [End Month] & " " & [End Day] & ", " & Year([Course End Date])),[Start Month] & " " & [Start Day] & ", " & Year([Course Start Date]))

AS [Session Duration], [Course Schedule].[# of Instructors], [Course Schedule].Capacity, [Course Schedule].[Primary Instructor], [Course Schedule].[2nd Instructor], [Course Schedule].[3rd Instructor], [Course Schedule].[# of Attendees], [List of Courses].[Course Duration], [List of Courses].Vendor, [List of Courses].[Primary HRA]

FROM [List of Courses]

INNER JOIN [Course Schedule]

ON [List of Courses].[Course ID] = [Course Schedule].[Course ID]

WHERE ((([List of Courses].[Primary HRA]) Like [To view HRA specific sessions, enter first name (or 1st 3 letters) otherwise leave blank:] & "*"))

ORDER BY [Course Schedule].[Course Start Date]

 

[namliam]

Try using this function in vba

Public Function MyDates(Startdate As Date, EndDate As Date) As String
    If Startdate = EndDate Then
        MyDates = Format(Startdate, "mmm. d. yyyy")
    ElseIf Startdate < EndDate Then
        If Month(Startdate) = Month(EndDate) Then
            MyDates = Format(Startdate, "mmm. d - ") & Format(EndDate, "d YYYY")
        Else
            MyDates = Format(Startdate, "mmm. d - ") & Format(EndDate, "mmm. d yyyy")
        End If
    Else
        MyDates = "Invalid"
    End If
        
End Function

You will get these results...
Mar. 1. 2004
Mar. 1 - 4 2004
Mar. 30 - Apr. 4 2004

BR

 

[alidaanish]

is there an easier way (maybe less efficient) ? cuz i dont even know where

i dont know where i would put this code in. i dont know vba at all!

Try using this function in vba

Public Function MyDates(Startdate As Date, EndDate As Date) As String
    If Startdate = EndDate Then
        MyDates = Format(Startdate, "mmm. d. yyyy")
    ElseIf Startdate < EndDate Then
        If Month(Startdate) = Month(EndDate) Then
            MyDates = Format(Startdate, "mmm. d - ") & Format(EndDate, "d YYYY")
        Else
            MyDates = Format(Startdate, "mmm. d - ") & Format(EndDate, "mmm. d yyyy")
        End If
    Else
        MyDates = "Invalid"
    End If
        
End Function

You will get these results...
Mar. 1. 2004
Mar. 1 - 4 2004
Mar. 30 - Apr. 4 2004

BR