Yaslaw.Info

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== [VBA][Access] Zeitreihe vervollständigen ======
//Code-Schnippsel: Löcher in einer Zeitreihe füllen//

[[http://www.tutorials.de/relationale-datenbanksysteme/398234-access-luecken-pivot-chart-darstellen.html#post2056166]]
<code vb>Public Sub completeTimeList()
    'Tabellennamen und SPaltennamen definieren
    Const C_TABLE_NAME As String = "[my_table]"
    Const C_TIME_COLUMN As String = "[messzeit]"
        
    'SQL-Spezifische Dinge defineren
    Const C_SQL_DATETIME_FORMAT As String = "\#mm\/dd\/yyyy hh:nn:ss\#"
    Const C_SQL_PATTERN As String = "INSERT INTO " & C_TABLE_NAME & " (" & C_TIME_COLUMN & ") VALUES (%s)"
    
    'Erster und letzter Eintrag auslesen
    Dim startTime   As Date:    startTime = DMin(C_TIME_COLUMN, C_TABLE_NAME)
    Dim endTime     As Date:    endTime = DMax(C_TIME_COLUMN, C_TABLE_NAME)
    
    'Differenz in Minuten berechnen
    Dim cntMinutes  As Long:    cntMinutes = DateDiff("n", startTime, endTime)
    Dim actTimeS    As String
    Dim delta       As Long
    
    'Über alle Differezminuten iterieren. Erste und letzt können ausgelassen werden
    For delta = 1 To cntMinutes - 1 Step 1
        'Datum berechenen: startDate + delta Minuten
        'Das ganez in ein SQL-String formatieren: #mm/dd/yyyy hh:nn:ss#
        actTimeS = format(DateAdd("n", delta, startTime), C_SQL_DATETIME_FORMAT)
        'Prüfen ob diese Uhrzeit in der Tabelle vorhanden ist
        If DCount("*", C_TABLE_NAME, C_TIME_COLUMN & " = " & actTimeS) = 0 Then
            'Wenn nicht, einfügen
            CurrentDb.execute replace(C_SQL_PATTERN, "%s", actTimeS)
        End If
    Next delta
End Sub</code>
Yaslaw.Info

User Tools

Site Tools

Page Tools
