Access-Paradies|ersten/letzten Tag eines Zeitraums ermitteln

'// ----------------------------------------------------------------------
'// In diesem Codeausschnitt werden folgende Funktionen dargestellt:
'// ----------------------------------------------------------------------
'// Funktion               |Rückgabe
'// ----------------------------------------------------------------------
'// FirstDayCurrentWeek    |Erster Tag der aktuellen Woche
'// LastDayCurrentWeek     |Letzter Tag der aktuellen Woche
'// FirstDayOfWeek         |Erster Tag einer beliebigen Woche
'// LastDayOfWeek          |Letzter Tag einer beliebigen Woche
'// ----------------------------------------------------------------------
'// FirstDayCurrentMonth   |Erster Tag des aktuellen Monats
'// LastDayCurrentMonth    |Letzter Tag des aktuellen Monats
'// FirstDayOfMonth        |Erster Tag eines beliebigen Monats
'// LastDayOfMonth         |Letzter Tag eines beliebigen Monats
'// ----------------------------------------------------------------------
'// FirstDayCurrentQuarter |Erster Tag in diesem Quartal
'// LastDayCurrentQuarter  |Letzter Tag in diesem Quartal
'// FirstDayInQuarter      |Erster Tag im Quartal eines beliebigen Datums
'// LastDayInQuarter       |Letzter Tag im Quartal eines beliebigen Datums
'// ----------------------------------------------------------------------
'// FirstDayCurrentYear    |Ersten Tag des aktuellen Jahres
'// LastDayCurrentYear     |Letzter Tag des aktuellen Jahres
'// FirstDayOfYear         |Erster Tag eines beliebigen Jahres
'// LastDayOfYear          |Letzter Tag eines beliebigen Jahres
'// ----------------------------------------------------------------------
 
Sub Beispiel()
 
    Debug.Print "Erster Tag der aktuellen Woche: "; FirstDayCurrentWeek()
    Debug.Print "Letzter Tag der aktuellen Woche: "; LastDayCurrentWeek()
    Debug.Print "Erster Tag einer beliebigen Woche: "; FirstDayOfWeek(Date)
    Debug.Print "Letzter Tag einer beliebigen Woche: "; LastDayOfWeek(Date)
    Debug.Print String(60, "-")
    Debug.Print "Erster Tag des aktuellen Monats: "; FirstDayCurrentMonth()
    Debug.Print "Letzter Tag des aktuellen Monats: "; LastDayCurrentMonth()
    Debug.Print "Erster Tag eines beliebigen Monats: "; FirstDayOfMonth(5, 2003)
    Debug.Print "Letzter Tag eines beliebigen Monats: "; LastDayOfMonth(5, 2003)
    Debug.Print String(60, "-")
    Debug.Print "Erster Tag in diesem Quartal: "; FirstDayCurrentQuarter()
    Debug.Print "Letzter Tag in diesem Quartal: "; LastDayCurrentQuarter()
    Debug.Print "Erster Tag im Quartal eines beliebigen Datums: "; FirstDayInQuarter(Date)
    Debug.Print "Letzter Tag im Quartal eines beliebigen Datums: "; LastDayInQuarter(Date)
    Debug.Print String(60, "-")
    Debug.Print "Ersten Tag des aktuellen Jahres: "; FirstDayCurrentYear()
    Debug.Print "Letzter Tag des aktuellen Jahres: "; LastDayCurrentYear()
    Debug.Print "Erster Tag eines beliebigen Jahres: "; FirstDayOfYear(2003)
    Debug.Print "Letzter Tag eines beliebigen Jahres: "; LastDayOfYear(2003)
 
 
End Sub
 
Function FirstDayCurrentWeek() As Date
'// ----------------------------------------------------------------------
'// Methode:   | Ersten Tag der aktuellen Woche ermitteln
'// ----------------------------------------------------------------------
'// Parameter: | -
'// ----------------------------------------------------------------------
'// Rückgabe:  | Datumswert (s.o.)
'// ----------------------------------------------------------------------
    FirstDayCurrentWeek = Date - (Weekday(Date, vbMonday) - 1)
 
End Function
 
Function LastDayCurrentWeek() As Date
'// ----------------------------------------------------------------------
'// Methode:   | Letzten Tag der aktuellen Woche ermitteln
'// ----------------------------------------------------------------------
'// Parameter: | -
'// ----------------------------------------------------------------------
'// Rückgabe:  | Datumswert (s.o.)
'// ----------------------------------------------------------------------
    LastDayCurrentWeek = Date + (7 - Weekday(Date, vbMonday))
 
End Function
 
Function FirstDayOfWeek(dtDate As Date) As Date
'// ----------------------------------------------------------------------
'// Methode:   | Ersten Tag einer beliebigen Woche ermitteln
'// ----------------------------------------------------------------------
'// Parameter: | Prüfdatum
'// ----------------------------------------------------------------------
'// Rückgabe:  | Datumswert (s.o.)
'// ----------------------------------------------------------------------
    FirstDayOfWeek = dtDate - (Weekday(dtDate, vbMonday) - 1)
 
End Function
 
Function LastDayOfWeek(dtDate As Date) As Date
'// ----------------------------------------------------------------------
'// Methode:   | Letzten Tag einer beliebigen Woche ermitteln
'// ----------------------------------------------------------------------
'// Parameter: | Prüfdatum
'// ----------------------------------------------------------------------
'// Rückgabe:  | Datumswert (s.o.)
'// ----------------------------------------------------------------------
    LastDayOfWeek = dtDate + (7 - Weekday(dtDate, vbMonday))
 
End Function
 
Function FirstDayCurrentMonth() As Date
'// ----------------------------------------------------------------------
'// Methode:   | Ermittelt den ersten Tag des aktuellen Monats
'// ----------------------------------------------------------------------
'// Parameter: | -
'// ----------------------------------------------------------------------
'// Rückgabe:  | Rückgabe eines Datums (s.o.)
'// ----------------------------------------------------------------------
    FirstDayCurrentMonth = CDate(Date - Day(Date) + 1)
 
End Function
 
Function LastDayCurrentMonth() As Date
'// ----------------------------------------------------------------------
'// Methode:   | Letzten Tag des aktuellen Monats ermitteln
'// ----------------------------------------------------------------------
'// Parameter: | -
'// ----------------------------------------------------------------------
'// Rückgabe:  | Rückgabe eines Datums (s.o.)
'// ----------------------------------------------------------------------
    LastDayCurrentMonth = CDate(DateAdd("m", 1, Date - Day(Date)))
 
End Function
 
Function FirstDayOfMonth(lMonth As Long, lYear As Long) As Date
'// ----------------------------------------------------------------------
'// Methode:   | Ersten Tag eines beliebigen Monats ermitteln
'// ----------------------------------------------------------------------
'// Parameter: | lMonth - Monatszahl (1-12)
'//            | lYear  - Jahreszahl
'// ----------------------------------------------------------------------
'// Rückgabe:  | Rückgabe eines Datums (s.o.)
'// ----------------------------------------------------------------------
    FirstDayOfMonth = DateSerial(lYear, lMonth, 1)
 
End Function
 
Function LastDayOfMonth(lMonth As Long, lYear As Long) As Date
'// ----------------------------------------------------------------------
'// Methode:   | Letzten Tag eines beliebigen Monats ermitteln
'// ----------------------------------------------------------------------
'// Parameter: | lMonth - Monatszahl (1-12)
'//            | lYear  - Jahreszahl
'// ----------------------------------------------------------------------
'// Rückgabe:  | Datumswert (s.o.)
'// ----------------------------------------------------------------------
    LastDayOfMonth = DateSerial(lYear, lMonth + 1, 0)
 
End Function
 
Function FirstDayCurrentQuarter() As Date
'// ----------------------------------------------------------------------
'// Methode:   | Erster Tag des aktuellen Quartals ermitteln
'// ----------------------------------------------------------------------
'// Parameter: | -
'// ----------------------------------------------------------------------
'// Rückgabe:  | Datumswert (s.o.)
'// ----------------------------------------------------------------------
    Dim dblValue As Double
    dblValue = (Month(Date) - 1) / 3
    FirstDayCurrentQuarter = _
    DateSerial(Year(Date), Int(dblValue) * 3 + 1, 1)
 
End Function
 
Function LastDayCurrentQuarter() As Date
'// ----------------------------------------------------------------------
'// Methode:   | Letzter Tag des aktuellen Quartals ermitteln
'// ----------------------------------------------------------------------
'// Parameter: | -
'// ----------------------------------------------------------------------
'// Rückgabe:  | Datumswert (s.o.)
'// ----------------------------------------------------------------------
    Dim dblValue As Double
    dblValue = (Month(Date) - 1) / 3
    LastDayCurrentQuarter = _
    DateSerial(Year(Date), Int(dblValue) * 3 + 4, 0)
 
End Function
 
Function FirstDayInQuarter(dtDate As Date) As Date
'// -----------------------------------------------------------------------------
'// Methode:   | Ermittelt den ersten Tag im Quartal eines beliebigen Datums
'// -----------------------------------------------------------------------------
'// Parameter: | dtDate - Prüfdatum
'// -----------------------------------------------------------------------------
'// Rückgabe:  | Datumswert (s.o.)
'// -----------------------------------------------------------------------------
    Dim dblValue As Double
    dblValue = (Month(dtDate) - 1) / 3
    FirstDayInQuarter = DateSerial(Year(dtDate), Int(dblValue) * 3 + 1, 1)
 
End Function
 
Function LastDayInQuarter(dtDate As Date) As Date
'// ------------------------------------------------------------------------------
'// Methode:   | Ermittelt den letzten Tag im Quartal eines beliebigen Datums
'// ------------------------------------------------------------------------------
'// Parameter: | dtDate - Prüfdatum
'// ------------------------------------------------------------------------------
'// Rückgabe:  | Datumswert (s.o.)
'// ------------------------------------------------------------------------------
    Dim dblValue As Double
    dblValue = (Month(dtDate) - 1) / 3
    LastDayInQuarter = DateSerial(Year(dtDate), Int(dblValue) * 3 + 4, 0)
 
End Function
 
Function FirstDayCurrentYear() As Date
'// ----------------------------------------------------------------------
'// Methode:   | Ersten Tag des aktuellen Jahres ermitteln
'// ----------------------------------------------------------------------
'// Parameter: | -
'// ----------------------------------------------------------------------
'// Rückgabe:  | Datumswert (s.o.)
'// ----------------------------------------------------------------------
    FirstDayCurrentYear = Date - DatePart("y", Date) + 1
 
End Function
 
Function LastDayCurrentYear() As Date
'// ----------------------------------------------------------------------
'// Methode:   | Letzten Tag des aktuellen Jahres ermitteln
'// ----------------------------------------------------------------------
'// Parameter: | -
'// ----------------------------------------------------------------------
'// Rückgabe:  | Datumswert (s.o.)
'// ----------------------------------------------------------------------
    LastDayCurrentYear = _
    DateAdd("yyyy", 1, Date - DatePart("y", Date))
 
End Function
 
Function FirstDayOfYear(lYear As Long) As Date
'// ----------------------------------------------------------------------
'// Methode:   | Ersten Tag eines beliebigen Jahres ermitteln
'// ----------------------------------------------------------------------
'// Parameter: | lYear - Jahreszahl
'// ----------------------------------------------------------------------
'// Rückgabe:  | Datumswert (s.o.)
'// ----------------------------------------------------------------------
    FirstDayOfYear = DateSerial(lYear, 1, 1)
 
End Function
 
Function LastDayOfYear(lYear As Long) As Date
'// ----------------------------------------------------------------------
'// Methode:   | Letzter Tag eines beliebigen Jahres ermitteln
'// ----------------------------------------------------------------------
'// Parameter: | lYear - Jahreszahl
'// ----------------------------------------------------------------------
'// Rückgabe:  | Datumswert (s.o.)
'// ----------------------------------------------------------------------
    LastDayOfYear = DateSerial(lYear, 12, 31)
 
End Function