I have three queries that I pull from
Tables:
Cadets----------------------CadetId,Name,SSN,PhaseID,
Cadet Dates----------------DateID,CadetID,SDID,DTG(Date/Time Group)
SpecificDates---------------SDID,Classification
Queries:
Monthly Invoice - Date (Crosstab)
TRANSFORM First([Cadets Dates].Date) AS FirstOfDate
SELECT [Cadets Dates].CadetID
FROM SpecificDates INNER JOIN [Cadets Dates] ON SpecificDates.SDID = [Cadets
Dates].SDID
GROUP BY [Cadets Dates].CadetID
PIVOT SpecificDates.Classification In ("Date of Entry","Exited Boot Camp");
Monthly Invoice - Cadets
SELECT [Cadets].[CadetID], [last] & ", " & [first] & " " & nz([middle]) AS
CadetName, [Cadets].[PhaseID]
FROM Cadets
WHERE ((([Cadets].[PhaseID])<5))
ORDER BY [last] & ", " & [first] & " " & nz([middle]);
Monthly Invoice - Date Query
SELECT [Monthly Invoices - Date].CadetID, [Monthly Invoice -
Cadets].CadetName, IIf(IsNull([Date of Entry]),"N/A",Format([date of
entry],'Medium Date')) AS DoE, IIf(IsNull([exited boot
camp]),"N/A",Format([exited boot camp],'Short Date')) AS DoExit,
CalcDaysofService([Date of Entry],[exited boot camp]) AS [Days of Service],
DatesofService([days of
service],IIf([doexit]="N/A","DoE","DoExit"),[DoE],[doExit]) AS [Dates of
Service], 80 AS Rate, Format([rate]*[days of service],'Currency') AS Cost
FROM [Monthly Invoice - Cadets] INNER JOIN [Monthly Invoices - Date] ON
[Monthly Invoice - Cadets].CadetID = [Monthly Invoices - Date].CadetID
ORDER BY [Monthly Invoice - Cadets].CadetName;
Modules:
Public Function NumofDays(month As Integer) As Integer
On Error GoTo Error_NumofDays
Select Case month
Case 1
NumofDays = 31
Case 2
NumofDays = 28
Case 3
NumofDays = 31
Case 4
NumofDays = 30
Case 5
NumofDays = 31
Case 6
NumofDays = 30
Case 7
NumofDays = 31
Case 8
NumofDays = 31
Case 9
NumofDays = 30
Case 10
NumofDays = 31
Case 11
NumofDays = 30
Case 12
NumofDays = 31
Case Else
NumofDays = 31
End Select
Exit_NumofDays:
Exit Function
Error_NumofDays:
MsgBox Err.Description
Resume Exit_NumofDays
End Function
Public Function CalcDaysofService(dEntry As Date, dExit As Variant) As Integer
On Error GoTo Err_CalcDaysofService
Dim dEndofMonth As Date
Dim dStartofMonth As Date
If IsDate(dExit) = False Then
If Format(dEntry, "yymm") = Format(Date, "yymm") Then
dEndofMonth = DateAdd("d", NumofDays(month(Date)), DateAdd("d",
-Day(Date), Date))
'response = MsgBox(dEndofMonth, vbOKOnly)
CalcDaysofService = diff2dates2("d", dEntry, dEndofMonth)
Else
CalcDaysofService = NumofDays(month(Date))
End If
Else
If Format(dExit, "yymm") = Format(Date, "yymm") Then
CalcDaysofService = Day(dExit)
Else
CalcDaysofService = NumofDays(month(Date))
End If
End If
Exit_CalcDaysofService:
Exit Function
Err_CalcDaysofService:
MsgBox Err.Description
Resume Exit_CalcDaysofService
End Function
Public Function DatesofService(iDaysofService As Integer, sType As String,
dDoE As Date, dDoExit As Date) As Variant
On Error GoTo Err_DatesofService
If iDaysofService < NumofDays(month(dDoE)) Then
Select Case sType
Case "DoE"
DatesofService = Day(dDoE) & " - " & NumofDays(month(DoE)) &
" " & Format(DoE, "mmm")
Case "DoExit"
'response = MsgBox(dDate, vbOKOnly)
DatesofService = "1 - " & Day(dDoExit) & " " &
Format(dDoExit, "mmm")
Case Else
DatesofService = "N/A"
End Select
Else
DatesofService = "1 - " & NumofDays(month(dDoExit)) & " " &
Format(dDoExit, "mmm")
End If
Exit_DatesofService:
Exit Function
Err_DatesofService:
MsgBox Err.Description
Resume Exit_DatesofService
End Function
The function DatesofService() does not work right now because I was trying
to figure out how to select different months, but it did work as shown below.
For the month of November
CadetName | DoE | DoExit | DaysofService | DatesofService
Cadet1 | 25-Jul-07 | N/A | 30 | 1 - 30
Nov
Cadet2 | 27-Mar-07| 12-Nov-07| 12 | 1 - 12 Nov
Cadet3 | 11-Nov-07| 22-Nov-07| 11 | 11 - 22 Nov
Some things that should be known:
-Every Cadet has a DoE(Date of Entry)
-Not every Cadet has a DoExit (date of Exit) until he has exited
-I did not put the dates into the Cadet Table because the names are used
alot more often than the dates and I did not want to be accessing that data
until needed. Plus, since creation point, I have added about 7 more different
Date Classifications making a table full of columns harder to maintain or
update.
And yes I modified your diff2dates() function to remove the
qualifier(months,days,etc) to get diff2dates2(), but anotated in the remarks
that it was your function with modifications.
