Visual Basic

Richard MacCutchan wrote:

But I do take exception to people being abusive on this site.

dtPick5.CustomFormat = "MM',' dd',' yyyy"

Private Sub tbStudentID2_TextChanged(sender As Object, e As EventArgs) Handles tbStudentID2.TextChanged
    Dim studentID As Integer
    If Not Integer.TryParse(tbStudentID2.Text, studentID) Then
        ' Not a valid ID
        txtDays.Text = String.Empty
    Else
        Using command As New OleDbCommand("SELECT DateDiff('d', [DateofRank], [currentDate]) FROM StudentData WHERE StudentID = @StudentID", myConnection)
            command.Parameters.AddWithValue("@StudentID", studentID)
            
            ConnDB()
            
            Dim days As Object = command.ExecuteScalar()
            txtDays.Text = If(Convert.IsDBNull(days), String.Empty, Convert.ToString(days))
        End Using
    End If
End Sub

Private Sub tbStudentID2_TextChanged(sender As Object, e As EventArgs) Handles tbStudentID2.TextChanged
        mySql = "SELECT DateDiff('d', [DateofRank], [currentDate]) FROM StudentData"
        '"SELECT DateDiff('d', [DateofRank], [currentDate]) FROM StudentData WHERE id={id}"
        ConnDB()
        'myCommand = New OleDbCommand(mySql, myConnection)
        'myCommand.ExecuteNonQuery()

        Dim studentID As Integer
        If Not Integer.TryParse(tbStudentID2.Text, studentID) Then
            ' Not a valid ID
            tbDays.Text = String.Empty
        Else
            Using command As New OleDbCommand("SELECT DateDiff('d', [DateofRank], [currentDate]) FROM StudentData WHERE StudentID = @StudentID", myConnection)
                command.Parameters.AddWithValue("@StudentID", studentID)

                ConnDB()

                Dim days As Object = command.ExecuteScalar()
                tbDays.Text = If(Convert.IsDBNull(days), String.Empty, Convert.ToString(days))
            End Using


            ' ADD PARAMETERS - ORDER MATTERS !!!
            Access.AddParam("@TIG", tbDays.Text)
            Access.AddParam("@SID", tbStudentID2.Text)

            ' RUN COMMAND
            Access.ExecQuery("UPDATE StudentData " &
                             "SET TimeinGrade=@TIG " &
                             "WHERE StudentID=@SID")

            ' REPORT & ABORT ON ERRORS
            If NoErrors(True) = False Then Exit Sub
        End If
    End Sub

UPDATE StudentData SET currentDate = DATE()"

Dim M9 = AmCommon.MakeMonthStartDate(-8)
Dim M10 = AmCommon.MakeMonthStartDate(-9)
Dim M11 = AmCommon.MakeMonthStartDate(-10)
Dim M12 = AmCommon.MakeMonthStartDate(-11)

Dim sM2 = AmCommon.MakeMonthStopDate(-1)
Dim sM3 = AmCommon.MakeMonthStopDate(-2)
Dim sM4 = AmCommon.MakeMonthStopDate(-3)
Dim sM5 = AmCommon.MakeMonthStopDate(-4)

Public Shared Function MakeMonthStartDate(byVal monthsBack as Integer) As DateTime

    Dim month = DateTime.Now().AddMonths(monthsBack)
    Dim fd = FirstDayOfMonth(month)
    Return New DateTime(month.Year, month.Month, fd.Day, 0, 0, 0)

End Function
Public Shared Function MakeMonthStopDate(byVal monthsBack as Integer) As DateTime

    Dim month = DateTime.Now().AddMonths(monthsBack)
    Dim ld = LastDayOfMonth(month)
    Return New DateTime(month.Year, month.Month, ld.Day, 23, 59, 59)

End Function

Public Shared Function FirstDayOfMonth(ByVal sourceDate As DateTime) As DateTime
    Return New DateTime(sourceDate.Year, sourceDate.Month, 1)
End Function

Public Shared Function LastDayOfMonth(ByVal sourceDate As DateTime) As DateTime

    Dim daysInMonth = DateTime.DaysInMonth(sourceDate.Year, sourceDate.Month)
    Return New DateTime(sourceDate.Year, sourceDate.Month, daysInMonth)

End Function

Dim Today As Date = Date.Today
Dim M1 As Date = Today.AddDays(1 - Today.Day)
Dim M2 As Date = M1.AddMonths(-1)
Dim M3 As Date = M1.AddMonths(-2) ' Or: M2.AddMonths(-1)
...

• >= 2020/02/01 And < 2020/03/01
• >= 2020/01/01 And < 2020/02/01
• >= 2019/12/01 And < 2020/01/01
• etc.