J
Joseph
This question has probably been asked, but I have dial up and not alot of
time to search.
I need to make an invoice of enrolled cadets during the month that count the
number of days that that cadet was enrolled, and have to be able to back
trace to previous months.
IE: For the Month of November
--Name---------DateofEntry-----------DateofExit--------NumofDays-------
Cadet1 | 2007-11-12 | 2007-11-29 | 17
Cadet2 | 2007-10-01 | N/A | 30
Here is the problem, I can get the formulas right for the current month, if
I can print it out while my computer time is set for the month that I am
requesting. But how can I get previous month
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.
The diff2dates2 function is a function created by D.J. Steele that I
modified to remove the qualifiers.
time to search.
I need to make an invoice of enrolled cadets during the month that count the
number of days that that cadet was enrolled, and have to be able to back
trace to previous months.
IE: For the Month of November
--Name---------DateofEntry-----------DateofExit--------NumofDays-------
Cadet1 | 2007-11-12 | 2007-11-29 | 17
Cadet2 | 2007-10-01 | N/A | 30
Here is the problem, I can get the formulas right for the current month, if
I can print it out while my computer time is set for the month that I am
requesting. But how can I get previous month
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.
The diff2dates2 function is a function created by D.J. Steele that I
modified to remove the qualifiers.