Lücken-Analyse mit VBA

Alternativ lassen sich die Lücken auch mit VBA finden. Das bietet sich vor allem dann an, wenn aufgrund der Lücken bestimmte Aktionen gestartet werden sollen.

Selbst wenn sich der Zahlenbereich spielend auf eine Million ausdehnen lässt, wird das irgendwann zu kompliziert oder nicht mehr funktionieren, weil vielleicht bestimmte Randbedingungen (nur gerade Zahlen, letzte Ziffer ist Prüfsumme, o.ä.) nicht erfüllt werden können.

Spätestens dann sollten Sie über eine Lösung mit VBA nachdenken. Aber auch erst dann, denn grundsätzlich sind SQL-Lösungen gerade bei vielen Datensätzen immer schneller als eine selbstprogrammierte Schleife in VBA.

Ich gehe mal davon aus, dass Sie schon wissen, wie Sie lesend oder schreibend auf Datensätze zugreifen und wie eine Schleife aussieht (sonst können Sie dies in den Beiträgen "Erzeugung von Recordset-Objekten" und "Überblick über Schleifen" nachlesen). Fügen Sie also den folgenden Code in ein beliebiges Modul ein:

Sub FindeLuecken()
    Dim rcsQuelle As DAO.Recordset
    Dim rcsZiel As DAO.Recordset
    Dim lngVorher As Long
    Dim lngNachher As Long
    Dim lngLuecke As Long
    
    'Zieltabelle leeren
    CurrentDb.Execute "DELETE * FROM tblLuecken"
    
    'Quelle und Ziel öffnen
    Set rcsQuelle = CurrentDb.OpenRecordset( _
        "SELECT Zahl2 FROM tblZwei ORDER BY Zahl2", dbOpenDynaset)
    Set rcsZiel = CurrentDb.OpenRecordset("tblLuecken", dbOpenDynaset)
    
    'untere Grenze festlegen
    lngVorher = 0
    Do Until rcsQuelle.EOF
        'nächsthöhere Zahl finden
        lngNachher = rcsQuelle.Fields("Zahl2").Value
        If lngNachher > lngVorher + 1 Then
            'also ist zwischen unterer und oberer Zahl eine Lücke
            For lngLuecke = lngVorher + 1 To lngNachher - 1
                'alle Zahlen dazwischen in Zieltabelle schreiben
                With rcsZiel
                    .AddNew
                    .Fields("ZahlLuecke").Value = lngLuecke
                    .Update
                End With
            Next
        End If
        'bisherige obere als untere Grenze festlegen
        lngVorher = lngNachher
        
        'zum nächsten Datensatz gehen
        rcsQuelle.MoveNext
    Loop
End Sub

Da als Ergebnis sehr viele Zahlen zurückgegeben werden könnten, ist es am einfachsten, diese auch wieder in eine Tabelle tblLuecken mit einem Long-Zahlenfeld ZahlLuecke zu schreiben. Damit diese Tabelle garantiert immer leer ist, werden darin mit Currentdb.Execute und der folgenden SQL-Löschanweisung alle Datensätze entfernt.

Die beiden Long-Variablen lngVorher und lngNachher enthalten jeweils immer die kleinere und größere Zahl zweier Datensätze. Diese werden daraufhin untersucht, ob dazwischen eine Lücke ist, die Differenz also mehr als 1 beträgt.

Dann werden alle diese Lücken-Zahlen dazwischen in die Zieltabelle geschrieben, die obere Grenze zur unteren gemacht und im nächsten Datensatz eine neue obere Grenze ermittelt.

Der Code nimmt als unterste Grenze die 0 an (wegen lngVorher = 0) und als obere Grenze den Datensatz mit der höchsten Nummer. Sie können daher die obere Grenze künstlich verschieben, indem Sie genau einen Datensatz mit der obersten Nummer hinzufügen.

Damit haben Sie verschiedene Techniken kennen gelernt, um Daten in Tabellen auch dann analysieren zu können, wenn gar keine Daten da sind.

Access: Lücken finden - Verknüpfungstricks mit Abfragen

Eine Liste ohne Vergleichstabelle: Lücken-Analyse mit VBA