Line-No. / Ref. | Code Line |
0001 | Public Sub Auto_Translate_Reference_Notes() |
0002 | 'This Sub translates and "fixes" "++++" & "+NN+" references in Notes & other Objects. This is to improve performance by obviating the need for look-ups. |
0003 | Dim rsKeyWords As Recordset |
0004 | Dim rsKeyWords_Update As Recordset |
0005 | Dim rsObject As Recordset |
0006 | Dim rsNotes_To_Regen As Recordset |
0007 | Dim rsAuto_Translate_Reference_Notes_Actions As Recordset |
0008 | Dim strQuery As String |
0009 | Dim iUpdates As Long |
0010 | Dim iUpdates_Total As Long |
0011 | Dim Duration As Single |
0012 | Dim RunStartTime As Date |
0013 | Dim strKeyWord As String |
0014 | Dim Object_Type As String |
0015 | Dim iObject_ID As Integer |
0016 | Dim strObject_Text As String |
0017 | Dim i As Long |
0018 | Dim k As Long |
0019 | Dim iNote As Integer |
0020 | Dim Link_OK As Boolean |
0021 | Dim The_Word As String |
0022 | Dim The_Word_OK As Boolean |
0023 | Dim Update_Object As Boolean |
0024 | Dim Updating_Run_Notes As Boolean |
0025 | Dim Update_Notes_Archive As Boolean |
0026 | Dim iCounter As Integer |
0027 | Dim In_Documentation As Boolean |
0028 | iUpdates_Total = 0 |
0029 | Updating_Run_Notes = False |
0030 | RunStartTime = Now() |
0031 | 'Clear the Notes_To_Regen table ... |
0032 | strQuery = "DELETE * FROM Notes_To_Regen;" |
0033 | DoCmd.RunSQL (strQuery) |
0034 | 'Ready the Log |
0035 | strQuery = "SELECT Auto_Translate_Reference_Notes_Actions.Timestamp_Logged, Auto_Translate_Reference_Notes_Actions.Object_Type, Auto_Translate_Reference_Notes_Actions.Object_ID, Auto_Translate_Reference_Notes_Actions.Timestamp, Auto_Translate_Reference_Notes_Actions.Tag_Found, Auto_Translate_Reference_Notes_Actions.Key_Word_Detected, Auto_Translate_Reference_Notes_Actions.Note_ID, Auto_Translate_Reference_Notes_Actions.Key_Word_Offset, Auto_Translate_Reference_Notes_Actions.Action_Taken FROM Auto_Translate_Reference_Notes_Actions WHERE (((Auto_Translate_Reference_Notes_Actions.Timestamp_Logged)=0));" |
0036 | Set rsAuto_Translate_Reference_Notes_Actions = CurrentDb.OpenRecordset(strQuery) |
0037 | 'Read & process the objects - Notes, Paper Abstracts, Comments, Book Abstracts, Comments, Archived Notes ... |
0038 | For iCounter = 1 To 14 |
0039 | iUpdates = 0 |
0040 | Select Case iCounter |
0041 | Case 1 |
0042 | strQuery = "SELECT Notes.ID, Notes.Item_Text, Notes.Status FROM Notes WHERE (((Notes.Item_Text) Like ""*++++*"")) ORDER BY Notes.ID;" |
0043 | strKeyWord = "++++" |
0044 | Object_Type = "Notes" |
0045 | Case 2 |
0046 | strQuery = "SELECT Notes.ID, Notes.Item_Text, Notes.Status FROM Notes WHERE (((Notes.Item_Text) Like ""*+NN+*"")) ORDER BY Notes.ID;" |
0047 | strKeyWord = "+NN+" |
0048 | Object_Type = "Notes" |
0049 | Case 3 |
0050 | strQuery = "SELECT Authors.Author_ID, Authors.Author_Narrative FROM Authors WHERE (((Authors.Author_Narrative) Like ""*++++*"")) ORDER BY Authors.Author_ID;" |
0051 | strKeyWord = "++++" |
0052 | Object_Type = "Authors" |
0053 | Case 4 |
0054 | strQuery = "SELECT Authors.Author_ID, Authors.Author_Narrative FROM Authors WHERE (((Authors.Author_Narrative) Like ""*+NN+*"")) ORDER BY Authors.Author_ID;" |
0055 | strKeyWord = "+NN+" |
0056 | Object_Type = "Authors" |
0057 | Case 5 |
0058 | strQuery = "SELECT Papers.ID, Papers.Abstract FROM Papers WHERE (((Papers.Abstract) Like ""*++++*"")) ORDER BY Papers.ID;" |
0059 | strKeyWord = "++++" |
0060 | Object_Type = "Paper Abstracts" |
0061 | Case 6 |
0062 | strQuery = "SELECT Papers.ID, Papers.Abstract FROM Papers WHERE (((Papers.Abstract) Like ""*+NN+*"")) ORDER BY Papers.ID;" |
0063 | strKeyWord = "+NN+" |
0064 | Object_Type = "Paper Abstracts" |
0065 | Case 7 |
0066 | strQuery = "SELECT Papers.ID, Papers.Comments FROM Papers WHERE (((Papers.Comments) Like ""*++++*"")) ORDER BY Papers.ID;" |
0067 | strKeyWord = "++++" |
0068 | Object_Type = "Paper Comments" |
0069 | Case 8 |
0070 | strQuery = "SELECT Papers.ID, Papers.Comments FROM Papers WHERE (((Papers.Comments) Like ""*+NN+*"")) ORDER BY Papers.ID;" |
0071 | strKeyWord = "+NN+" |
0072 | Object_Type = "Paper Comments" |
0073 | Case 9 |
0074 | strQuery = "SELECT Books.ID1, Books.Abstract FROM Books WHERE (((Books.Abstract) Like ""*++++*"")) ORDER BY Books.ID1;" |
0075 | strKeyWord = "++++" |
0076 | Object_Type = "Book Abstracts" |
0077 | Case 10 |
0078 | strQuery = "SELECT Books.ID1, Books.Abstract FROM Books WHERE (((Books.Abstract) Like ""*+NN+*"")) ORDER BY Books.ID1;" |
0079 | strKeyWord = "+NN+" |
0080 | Object_Type = "Book Abstracts" |
0081 | Case 11 |
0082 | strQuery = "SELECT Books.ID1, Books.Comments FROM Books WHERE (((Books.Comments) Like ""*++++*"")) ORDER BY Books.ID1;" |
0083 | strKeyWord = "++++" |
0084 | Object_Type = "Book Comments" |
0085 | Case 12 |
0086 | strQuery = "SELECT Books.ID1, Books.Comments FROM Books WHERE (((Books.Comments) Like ""*+NN+*"")) ORDER BY Books.ID1;" |
0087 | strKeyWord = "+NN+" |
0088 | Object_Type = "Book Comments" |
0089 | Case 13 |
0090 | strQuery = "SELECT Notes_Archive.ID, Notes_Archive.Item_Text, Notes_Archive.Timestamp FROM Notes_Archive WHERE (((Notes_Archive.Item_Text) Like ""*++++*"")) ORDER BY Notes_Archive.ID;" |
0091 | strKeyWord = "++++" |
0092 | Object_Type = "Notes_Archive" |
0093 | Case 14 |
0094 | strQuery = "SELECT Notes_Archive.ID, Notes_Archive.Item_Text, Notes_Archive.Timestamp FROM Notes_Archive WHERE (((Notes_Archive.Item_Text) Like ""*+NN+*"")) ORDER BY Notes_Archive.ID;" |
0095 | strKeyWord = "+NN+" |
0096 | Object_Type = "Notes_Archive" |
0097 | End Select |
0098 | Set rsObject = CurrentDb.OpenRecordset(strQuery) |
0099 | If Not rsObject.EOF Then |
0100 | rsObject.MoveFirst |
0101 | Do Until rsObject.EOF |
0102 | iObject_ID = rsObject.Fields(0) |
0103 | strObject_Text = rsObject.Fields(1) |
0104 | Update_Object = False |
0105 | If iCounter >= 1 And iCounter <= 2 Then 'Notes only! |
0106 | Update_Notes_Archive = False |
0107 | 'Check the Archived Note |
0108 | If rsObject.Fields(2) = "Temp" Then |
0109 | 'Don't bother for Temp Notes |
0110 | Else |
0111 | 'Find the latest Archive Note |
0112 | strQuery = "SELECT Notes_Archive.ID, Notes_Archive.Item_Text, Notes_Archive.Timestamp FROM Notes_Archive WHERE (((Notes_Archive.ID) = " & iObject_ID & ")) ORDER BY Notes_Archive.Timestamp DESC;" |
0113 | Set rsKeyWords_Update = CurrentDb.OpenRecordset(strQuery) |
0114 | If Not rsKeyWords_Update.EOF Then |
0115 | If strObject_Text = rsKeyWords_Update.Fields(1) Then |
0116 | Update_Notes_Archive = True |
0117 | End If |
0118 | End If |
0119 | End If |
0120 | End If |
0121 | 'Find the next strKeyWord |
0122 | i = 1 |
0123 | Link_OK = False |
0124 | Do Until i = 0 |
0125 | i = InStr(i, strObject_Text, strKeyWord) |
0126 | If i > 0 Then |
0127 | Link_OK = True |
0128 | Else |
0129 | Link_OK = False |
0130 | End If |
0131 | If Link_OK = True Then |
0132 | k = FindWord(strObject_Text, i, "]") 'Find Start of word |
0133 | If k > 1 Then |
0134 | The_Word_OK = True |
0135 | The_Word = Mid(strObject_Text, k, i - k) |
0136 | 'Check no Bracket at start ... |
0137 | If Mid(strObject_Text, i - 1, 1) = "]" Then |
0138 | The_Word = Mid(The_Word, 2, Len(The_Word) - 2) |
0139 | End If |
0140 | 'Translate ... |
0141 | strQuery = "SELECT Note_Alternates.ID FROM Note_Alternates WHERE (((Note_Alternates.Item_Alt_Title)=""" & The_Word & """));" |
0142 | Set rsKeyWords = CurrentDb.OpenRecordset(strQuery) |
0143 | If rsKeyWords.EOF Then |
0144 | Debug.Print Now() & " - "; "Incorrect Keyword: "; Object_Type; iObject_ID; The_Word |
0145 | iNote = 0 |
0146 | Else |
0147 | rsKeyWords.MoveFirst |
0148 | iNote = rsKeyWords.Fields(0) |
0149 | strObject_Text = Left(strObject_Text, i + 1) & iNote & Mid(strObject_Text, i + 2) |
0150 | Update_Object = True |
0151 | End If |
0152 | The_Word = Trim(The_Word) |
0153 | In_Documentation = False |
0154 | If The_Word = "" Then 'Check for documentation ... ie. "+NN+" and the like ... |
0155 | If Mid(strObject_Text, i - 1, 1) <> " " And Mid(strObject_Text, k + 4, 1) <> " " Then |
0156 | In_Documentation = True |
0157 | End If |
0158 | End If |
0159 | If In_Documentation = False Then |
0160 | 'Log |
0161 | rsAuto_Translate_Reference_Notes_Actions.AddNew |
0162 | rsAuto_Translate_Reference_Notes_Actions.Fields(0) = Now() |
0163 | rsAuto_Translate_Reference_Notes_Actions.Fields(1) = Object_Type |
0164 | rsAuto_Translate_Reference_Notes_Actions.Fields(2) = iObject_ID |
0165 | If iCounter >= 13 Then |
0166 | rsAuto_Translate_Reference_Notes_Actions.Fields(3) = rsObject.Fields(2) |
0167 | End If |
0168 | rsAuto_Translate_Reference_Notes_Actions.Fields(4) = strKeyWord |
0169 | rsAuto_Translate_Reference_Notes_Actions.Fields(5) = The_Word |
0170 | rsAuto_Translate_Reference_Notes_Actions.Fields(6) = iNote |
0171 | rsAuto_Translate_Reference_Notes_Actions.Fields(7) = k |
0172 | If iNote > 0 Then |
0173 | rsAuto_Translate_Reference_Notes_Actions.Fields(8) = "Object updated OK" |
0174 | End If |
0175 | rsAuto_Translate_Reference_Notes_Actions.Update |
0176 | End If |
0177 | End If |
0178 | If Update_Object = True Then |
0179 | 'Write out to Notes_To_Regen if it doesn't already exist |
0180 | If iCounter >= 1 And iCounter <= 2 Then 'Notes only! |
0181 | Updating_Run_Notes = True |
0182 | strQuery = "SELECT * FROM Notes_To_Regen WHERE Note_ID=" & iObject_ID & ";" |
0183 | Set rsNotes_To_Regen = CurrentDb.OpenRecordset(strQuery) |
0184 | If rsNotes_To_Regen.EOF Then |
0185 | rsNotes_To_Regen.AddNew |
0186 | rsNotes_To_Regen.Fields(0) = iObject_ID |
0187 | rsNotes_To_Regen.Fields(1) = Now() |
0188 | rsNotes_To_Regen.Update |
0189 | End If |
0190 | End If |
0191 | End If |
0192 | i = i + 1 |
0193 | End If |
0194 | Loop |
0195 | If Update_Object = True Then |
0196 | iUpdates = iUpdates + 1 |
0197 | 'Update the Object |
0198 | rsObject.Edit |
0199 | rsObject.Fields(1) = strObject_Text |
0200 | rsObject.Update |
0201 | If iCounter >= 1 And iCounter <= 2 Then 'Notes only! |
0202 | If Update_Notes_Archive = True Then |
0203 | rsKeyWords_Update.Edit |
0204 | rsKeyWords_Update.Fields(1) = strObject_Text |
0205 | rsKeyWords_Update.Update |
0206 | iUpdates_Total = iUpdates_Total + 1 |
0207 | End If |
0208 | End If |
0209 | End If |
0210 | rsObject.MoveNext |
0211 | Loop |
0212 | End If |
0213 | Debug.Print Now() & " - "; Object_Type; " "; strKeyWord; iUpdates; "Updates" |
0214 | iUpdates_Total = iUpdates_Total + iUpdates |
0215 | Next iCounter |
0216 | 'Output the Notes Pages |
0217 | strQuery = "SELECT * FROM Notes_To_Regen;" |
0218 | Set rsNotes_To_Regen = CurrentDb.OpenRecordset(strQuery) |
0219 | If rsNotes_To_Regen.EOF Then |
0220 | i = 0 |
0221 | Else |
0222 | rsNotes_To_Regen.MoveLast |
0223 | i = rsNotes_To_Regen.RecordCount |
0224 | End If |
0225 | If i > 0 Then |
0226 | If MsgBox("Output " & i & " updated Notes?", vbYesNo) = vbYes Then |
0227 | Archive_Notes_Now = "No" |
0228 | Regenerate_the_Links = "No" |
0229 | Regen_Notes_Only = "Yes" |
0230 | CreateNotesWebPages |
0231 | End If |
0232 | End If |
0233 | 'Tidy Up |
0234 | Set rsKeyWords = Nothing |
0235 | Set rsObject = Nothing |
0236 | Set rsNotes_To_Regen = Nothing |
0237 | Set rsKeyWords_Update = Nothing |
0238 | Set rsAuto_Translate_Reference_Notes_Actions = Nothing |
0239 | DoCmd.OpenQuery ("Auto_Translate_Reference_Notes_Actions_List") |
0240 | Debug.Print Now() & " - "; " Total"; iUpdates_Total; "Updates" |
0241 | Duration = Round((Now() - RunStartTime) * 24 * 60, 1) |
0242 | If Duration < 1 Then |
0243 | Duration = Round((Now() - RunStartTime) * 24 * 60 * 60) |
0244 | MsgBox Now() & " - Automatic Note Linkage Translations Completed in " & Duration & " seconds. " & iUpdates_Total & " changes made.", vbOKOnly, "Automatic Note Linkages" |
0245 | Else |
0246 | MsgBox Now() & " - Automatic Note Linkages Translations Completed in " & Duration & " minutes. " & iUpdates_Total & " changes made.", vbOKOnly, "Automatic Note Linkages" |
0247 | End If |
0248 | End Sub |
Line-No. / Ref. | Code Line |
0001 | Public Function Functor_08(Note_ID, Note_Title, Note_Text) |
0002 | 'Insert Stats into Quarterly Reports |
0003 | Dim rs As Recordset |
0004 | Dim strQuery As String |
0005 | Dim Note_Text_Local As String |
0006 | Dim iStart_Reporting_Month As Integer |
0007 | Dim iReporting_Year As Integer |
0008 | Dim Qtr_Days As Single |
0009 | Dim Qtr_Fraction_Gone As Single |
0010 | Dim Hours_Left_Today As Single |
0011 | Dim strQuarter_From As String |
0012 | Dim strQuarter_To As String |
0013 | Dim strQuarter_Long As String |
0014 | Dim strQuarter_Short As String |
0015 | Dim str_Period_Start As String |
0016 | Dim QTDHrs As Single |
0017 | Dim YTDHrs As Single |
0018 | Dim QTDHrs_Total As Single |
0019 | Dim YTDHrs_Total As Single |
0020 | Dim QTD_Percent_Plan As Single |
0021 | Dim YTD_Percent_Plan As Single |
0022 | Dim QTD_Overall_Percent As Single |
0023 | Dim QTD_Overall_Percent_Plan As Single |
0024 | Dim YTD_Overall_Percent As Single |
0025 | Dim YTD_Overall_Percent_Plan As Single |
0026 | Dim QTDPlan As Single |
0027 | Dim YTDPlan As Single |
0028 | Dim QTDPlan_Overall As Single |
0029 | Dim YTDPlan_Overall As Single |
0030 | Dim YTDPlan_Footnote As String |
0031 | Dim Current_Qtr As String |
0032 | Dim strYear As String |
0033 | Dim strQuarter As String |
0034 | Dim Last_Year As Boolean |
0035 | YTDPlan_Footnote = "++FNIf zero hours are planned, ""%age against plan"" is meaningless, and appears as zero. ++" |
0036 | 'Find the Report Year & Quarter |
0037 | OK = Find_Report_Period(Note_Title, strYear, strQuarter) |
0038 | Set rs = CurrentDb.OpenRecordset("SELECT * FROM Next_Reporting_Month;") |
0039 | rs.MoveFirst |
0040 | iStart_Reporting_Month = rs.Fields(1) |
0041 | Current_Qtr = rs.Fields(8) |
0042 | iReporting_Year = rs.Fields(5) |
0043 | Qtr_Days = rs.Fields(11) |
0044 | Hours_Left_Today = rs.Fields(12) |
0045 | Qtr_Days = Qtr_Days - Hours_Left_Today / 7 |
0046 | Qtr_Fraction_Gone = rs.Fields(10) / rs.Fields(11) |
0047 | 'Check if running for the previous Quarter - ie. after the roll-over. |
0048 | Last_Year = False |
0049 | If Right(strQuarter, 1) <> Right(Current_Qtr, 1) Then |
0050 | 'Report for the last quarter of last academic year |
0051 | Last_Year = True |
0052 | iStart_Reporting_Month = CInt(Right(strQuarter, 1)) |
0053 | iStart_Reporting_Month = 1 + (iStart_Reporting_Month - 1) * 3 |
0054 | Current_Qtr = Right(strYear, 2) & strQuarter |
0055 | iReporting_Year = CInt(strYear) |
0056 | If strQuarter <> "Q4" Then |
0057 | iReporting_Year = iReporting_Year - 1 |
0058 | End If |
0059 | If strQuarter <> "Q3" Then |
0060 | Last_Year = False 'This used to say 'True' ...???! |
0061 | End If |
0062 | Qtr_Days = 365 / 4 'Dummy figure |
0063 | Hours_Left_Today = 0 |
0064 | Qtr_Fraction_Gone = 1 |
0065 | End If |
0066 | Select Case iStart_Reporting_Month |
0067 | Case 1 |
0068 | strQuarter_Long = "January - March" |
0069 | strQuarter_Short = "Q1" |
0070 | strQuarter_From = iReporting_Year + 1 & "-01" |
0071 | strQuarter_To = iReporting_Year + 1 & "-03" |
0072 | Case 4 |
0073 | strQuarter_Long = "April - June" |
0074 | strQuarter_Short = "Q2" |
0075 | strQuarter_From = iReporting_Year + 1 & "-04" |
0076 | strQuarter_To = iReporting_Year + 1 & "-06" |
0077 | Case 7 |
0078 | strQuarter_Long = "July - September" |
0079 | strQuarter_Short = "Q3" |
0080 | strQuarter_From = iReporting_Year + 1 & "-07" |
0081 | strQuarter_To = iReporting_Year + 1 & "-09" |
0082 | Case 10 |
0083 | strQuarter_Long = "October - December" |
0084 | strQuarter_Short = "Q4" |
0085 | strQuarter_From = iReporting_Year & "-10" |
0086 | strQuarter_To = iReporting_Year & "-12" |
0087 | End Select |
0088 | If iStart_Reporting_Month < 10 Then |
0089 | strQuarter_Long = strQuarter_Long & " " & iReporting_Year + 1 |
0090 | strQuarter_Short = Right(iReporting_Year + 1, 2) & strQuarter_Short |
0091 | Else |
0092 | strQuarter_Long = strQuarter_Long & " " & iReporting_Year |
0093 | strQuarter_Short = Right(iReporting_Year, 2) & strQuarter_Short |
0094 | End If |
0095 | str_Period_Start = "October " & iReporting_Year |
0096 | Set rs = Nothing |
0097 | If Last_Year = True Then |
0098 | strQuery = "SELECT Sum(Year_Crosstab_Old.Hours) AS SumOfHours FROM Projects INNER JOIN Year_Crosstab_Old ON Projects.Project_Name = Year_Crosstab_Old.Group WHERE (((Year_Crosstab_Old.Period) >= """ & strQuarter_From & """ And (Year_Crosstab_Old.Period) <= """ & strQuarter_To & """)) GROUP BY Projects.Status_Note HAVING (((Projects.Status_Note)=" & Note_ID & "));" |
0099 | Else |
0100 | strQuery = "SELECT Sum(Year_Crosstab.Hours) AS SumOfHours FROM Projects INNER JOIN Year_Crosstab ON Projects.Project_Name = Year_Crosstab.Group WHERE (((Year_Crosstab.Period) >= """ & strQuarter_From & """ And (Year_Crosstab.Period) <= """ & strQuarter_To & """)) GROUP BY Projects.Status_Note HAVING (((Projects.Status_Note)=" & Note_ID & "));" |
0101 | End If |
0102 | Set rs = CurrentDb.OpenRecordset(strQuery) |
0103 | If rs.EOF Then |
0104 | QTDHrs = 0 |
0105 | Else |
0106 | rs.MoveFirst |
0107 | QTDHrs = rs.Fields(0) |
0108 | End If |
0109 | Set rs = Nothing |
0110 | If Last_Year = True Then |
0111 | strQuery = "SELECT Sum(Year_Crosstab_Old.Hours) AS SumOfHours FROM Projects INNER JOIN Year_Crosstab_Old ON Projects.Project_Name = Year_Crosstab_Old.Group WHERE (((Year_Crosstab_Old.Period) >= """ & iReporting_Year & "-10" & """ And (Year_Crosstab_Old.Period) <= """ & strQuarter_To & """)) GROUP BY Projects.Status_Note HAVING (((Projects.Status_Note)=" & Note_ID & "));" |
0112 | Else |
0113 | strQuery = "SELECT Sum(Year_Crosstab.Hours) AS SumOfHours FROM Projects INNER JOIN Year_Crosstab ON Projects.Project_Name = Year_Crosstab.Group WHERE (((Year_Crosstab.Period) >= """ & iReporting_Year & "-10" & """ And (Year_Crosstab.Period) <= """ & strQuarter_To & """)) GROUP BY Projects.Status_Note HAVING (((Projects.Status_Note)=" & Note_ID & "));" |
0114 | End If |
0115 | Set rs = CurrentDb.OpenRecordset(strQuery) |
0116 | If rs.EOF Then |
0117 | YTDHrs = 0 |
0118 | Else |
0119 | rs.MoveFirst |
0120 | YTDHrs = rs.Fields(0) |
0121 | End If |
0122 | Set rs = Nothing |
0123 | If Last_Year = True Then |
0124 | strQuery = "SELECT Sum(Year_Crosstab_Old.Hours) AS SumOfHours FROM Projects INNER JOIN Year_Crosstab_Old ON Projects.Project_Name = Year_Crosstab_Old.Group WHERE (((Year_Crosstab_Old.Period) >= """ & strQuarter_From & """ And (Year_Crosstab_Old.Period) <= """ & strQuarter_To & """));" |
0125 | Else |
0126 | strQuery = "SELECT Sum(Year_Crosstab.Hours) AS SumOfHours FROM Projects INNER JOIN Year_Crosstab ON Projects.Project_Name = Year_Crosstab.Group WHERE (((Year_Crosstab.Period) >= """ & strQuarter_From & """ And (Year_Crosstab.Period) <= """ & strQuarter_To & """));" |
0127 | End If |
0128 | Set rs = CurrentDb.OpenRecordset(strQuery) |
0129 | rs.MoveFirst |
0130 | QTDHrs_Total = Nz(rs.Fields(0)) |
0131 | Set rs = Nothing |
0132 | If QTDHrs_Total = 0 Then |
0133 | QTDHrs_Total = 0.0001 |
0134 | End If |
0135 | QTD_Overall_Percent = QTDHrs / QTDHrs_Total * 100 |
0136 | If Last_Year = True Then |
0137 | strQuery = "SELECT Sum(Year_Crosstab_Old.Hours) AS SumOfHours FROM Projects INNER JOIN Year_Crosstab_Old ON Projects.Project_Name = Year_Crosstab_Old.Group WHERE (((Year_Crosstab_Old.Period) >= """ & iReporting_Year & "-10" & """ And (Year_Crosstab_Old.Period) <= """ & strQuarter_To & """));" |
0138 | Else |
0139 | strQuery = "SELECT Sum(Year_Crosstab.Hours) AS SumOfHours FROM Projects INNER JOIN Year_Crosstab ON Projects.Project_Name = Year_Crosstab.Group WHERE (((Year_Crosstab.Period) >= """ & iReporting_Year & "-10" & """ And (Year_Crosstab.Period) <= """ & strQuarter_To & """));" |
0140 | End If |
0141 | Set rs = CurrentDb.OpenRecordset(strQuery) |
0142 | rs.MoveFirst |
0143 | YTDHrs_Total = Nz(rs.Fields(0)) |
0144 | Set rs = Nothing |
0145 | If YTDHrs_Total = 0 Then |
0146 | YTDHrs_Total = 0.0001 |
0147 | End If |
0148 | YTD_Overall_Percent = YTDHrs / YTDHrs_Total * 100 |
0149 | strQuery = "SELECT Sum(Project_Plans.Weekly_Hours) AS SumOfWeekly_Hours FROM Projects INNER JOIN Project_Plans ON Projects.Project_Name = Project_Plans.Project WHERE (((Projects.Status_Note)=" & Note_ID & ") AND ((Project_Plans.Period)>=""" & strQuarter_From & """ And (Project_Plans.Period)<=""" & strQuarter_To & """));" |
0150 | Set rs = CurrentDb.OpenRecordset(strQuery) |
0151 | rs.MoveFirst |
0152 | QTDPlan = Nz(rs.Fields(0)) |
0153 | QTDPlan = QTDPlan / 3 / 7 * Qtr_Days * Qtr_Fraction_Gone |
0154 | Set rs = Nothing |
0155 | If QTDPlan = 0 Then |
0156 | QTD_Percent_Plan = 0 |
0157 | Else |
0158 | QTD_Percent_Plan = QTDHrs / QTDPlan * 100 |
0159 | End If |
0160 | strQuery = "SELECT Sum(Project_Plans.Weekly_Hours) AS SumOfWeekly_Hours FROM Projects INNER JOIN Project_Plans ON Projects.Project_Name = Project_Plans.Project WHERE (((Projects.Status_Note)=" & Note_ID & ") AND ((Project_Plans.Period)>=""" & iReporting_Year & "-10" & """ And (Project_Plans.Period)<""" & strQuarter_From & """));" |
0161 | Set rs = CurrentDb.OpenRecordset(strQuery) |
0162 | rs.MoveFirst |
0163 | YTDPlan = Nz(rs.Fields(0)) / 3 / 7 * 365 / 4 |
0164 | YTDPlan = YTDPlan + QTDPlan |
0165 | Set rs = Nothing |
0166 | If YTDPlan = 0 Then |
0167 | YTD_Percent_Plan = 0 |
0168 | Else |
0169 | YTD_Percent_Plan = YTDHrs / YTDPlan * 100 |
0170 | End If |
0171 | strQuery = "SELECT Sum(Project_Plans.Weekly_Hours) AS SumOfWeekly_Hours FROM Projects INNER JOIN Project_Plans ON Projects.Project_Name = Project_Plans.Project WHERE (((Project_Plans.Period)>=""" & strQuarter_From & """ And (Project_Plans.Period)<=""" & strQuarter_To & """));" |
0172 | Set rs = CurrentDb.OpenRecordset(strQuery) |
0173 | rs.MoveFirst |
0174 | QTDPlan_Overall = rs.Fields(0) |
0175 | QTDPlan_Overall = QTDPlan_Overall / 3 / 7 * Qtr_Days * Qtr_Fraction_Gone |
0176 | Set rs = Nothing |
0177 | QTD_Overall_Percent_Plan = QTDPlan / QTDPlan_Overall * 100 |
0178 | 'strQuery = "SELECT Sum(Project_Plans.Weekly_Hours) AS SumOfWeekly_Hours FROM Projects INNER JOIN Project_Plans ON Projects.Project_Name = Project_Plans.Project WHERE (((Project_Plans.Period)>=""" & iReporting_Year & "-10" & """ And (Project_Plans.Period)<=""" & iReporting_Year & "-" & strQuarter_To & """));" |
0179 | 'The above query was incorrect, and may explain some of the muddles below ... |
0180 | strQuery = "SELECT Sum(Project_Plans.Weekly_Hours) AS SumOfWeekly_Hours FROM Projects INNER JOIN Project_Plans ON Projects.Project_Name = Project_Plans.Project WHERE (((Project_Plans.Period)>=""" & iReporting_Year & "-10" & """ And (Project_Plans.Period)<=""" & strQuarter_To & """));" |
0181 | Set rs = CurrentDb.OpenRecordset(strQuery) |
0182 | rs.MoveFirst |
0183 | '***** THIS CODE NEEDS SORTING OUT - SERIES OF BODGES ************** |
0184 | 'YTDPlan_Overall = rs.Fields(0) / 3 / 7 * 365 / 4 ' ... replaced for 2019_Q3 report |
0185 | 'YTDPlan_Overall = rs.Fields(0) / 3 / 7 * 365 ' ... replaced for 2019_Q4 report |
0186 | 'YTDPlan_Overall = YTDPlan_Overall + QTDPlan_Overall 'THIS WAS COMMENTED OUT prior to the 19Q2 report - why? |
0187 | 'Looks like it should have been commented out!! Removed for 19Q3 report |
0188 | If Right(Current_Qtr, 2) = "Q4" Then |
0189 | 'Force equality! |
0190 | YTDPlan_Overall = QTDPlan_Overall |
0191 | YTD_Overall_Percent_Plan = QTD_Overall_Percent_Plan |
0192 | Else |
0193 | YTDPlan_Overall = rs.Fields(0) / 3 / 7 * 365 / 4 |
0194 | 'If Right(Current_Qtr, 2) = "Q1" Then 'Bodge put in 02/04/2020 ... sort it out when I have more time! |
0195 | ' YTDPlan_Overall = YTDPlan_Overall + QTDPlan_Overall |
0196 | 'End If |
0197 | 'Taken out on 01/04/2021! I had not "rolled over" this time |
0198 | YTD_Overall_Percent_Plan = YTDPlan / YTDPlan_Overall * 100 |
0199 | End If |
0200 | Set rs = Nothing |
0201 | If Note_ID = 512 Then |
0202 | Note_Text_Local = "|.|In " & strQuarter_Short |
0203 | Note_Text_Local = Note_Text_Local & " I spent across my various projects " & Round(QTDHrs_Total, 2) |
0204 | Note_Text_Local = Note_Text_Local & " hours (" & Round(YTDHrs_Total, 2) |
0205 | Note_Text_Local = Note_Text_Local & " hours YTD) – where by ""YTD"" - Year to Date - I mean the academic year commencing in " & str_Period_Start |
0206 | Note_Text_Local = Note_Text_Local & ". ""Actual versus plan"" was " & Round(QTDHrs_Total / QTDPlan_Overall * 100, 1) |
0207 | Note_Text_Local = Note_Text_Local & "%, (" & Round(YTDHrs_Total / YTDPlan_Overall * 100, 1) |
0208 | Note_Text_Local = Note_Text_Local & "% YTD)." |
0209 | Else |
0210 | If QTDPlan = 0 Then |
0211 | Note_Text_Local = "Summary of Progress during " & Right(iReporting_Year, 2) & "Q4" & " - " & Current_Qtr |
0212 | Note_Text_Local = Note_Text_Local & "|99||1|I have spent " & Round(YTDHrs, 2) |
0213 | Note_Text_Local = Note_Text_Local & " hour" & IIf(Round(YTDHrs, 2) = 1, "", "s") & " YTD on this Project, or related work, where for ""YTD"" - Year to Date - I mean the (academic) year that commenced in " & str_Period_Start |
0214 | Note_Text_Local = Note_Text_Local & "). That's " & Round(YTD_Percent_Plan, 1) |
0215 | Note_Text_Local = Note_Text_Local & "% of the planned" & IIf(YTDPlan = 0, YTDPlan_Footnote, "") & " effort. Overall, " & Round(YTD_Overall_Percent, 1) |
0216 | Note_Text_Local = Note_Text_Local & "% of my Project effort YTD has been directed towards this project." |
0217 | Else |
0218 | Note_Text_Local = "Summary of Progress during " & strQuarter_Long |
0219 | Note_Text_Local = Note_Text_Local & "|99||1|I spent " & Round(QTDHrs, 2) |
0220 | Note_Text_Local = Note_Text_Local & " hour" & IIf(Round(QTDHrs, 2) = 1, "", "s") & " in " & strQuarter_Short |
0221 | Note_Text_Local = Note_Text_Local & " on this Project, or related work (" & Round(YTDHrs, 2) |
0222 | Note_Text_Local = Note_Text_Local & " hour" & IIf(Round(YTDHrs, 2) = 1, "", "s") & " YTD, where for ""YTD"" - Year to Date - I mean the (academic) year that commenced in " & str_Period_Start |
0223 | Note_Text_Local = Note_Text_Local & "). That's " & Round(QTD_Percent_Plan, 1) |
0224 | Note_Text_Local = Note_Text_Local & "% of the planned effort (" & Round(YTD_Percent_Plan, 1) |
0225 | Note_Text_Local = Note_Text_Local & "% YTD). Overall, " & Round(QTD_Overall_Percent, 1) |
0226 | Note_Text_Local = Note_Text_Local & "% of my Project effort in the Quarter was directed towards this project (making " & Round(YTD_Overall_Percent, 1) |
0227 | Note_Text_Local = Note_Text_Local & "% YTD) - as against " & Round(QTD_Overall_Percent_Plan, 1) |
0228 | Note_Text_Local = Note_Text_Local & "% planned (" & Round(YTD_Overall_Percent_Plan, 1) |
0229 | Note_Text_Local = Note_Text_Local & "% YTD)." |
0230 | End If |
0231 | End If |
0232 | Note_Text = Note_Text_Local |
0233 | Functor_08 = "Yes" |
0234 | End Function |