Office: (Office 2016) VBA SQL-Anfrage mit Array

Helfe beim Thema VBA SQL-Anfrage mit Array in Microsoft Excel Hilfe um das Problem gemeinsam zu lösen; Hallo Zusammen Ich bin mal wieder auf ein Problem gestoßen, bei dem ich nicht weiterkomme. In VBA eine SQL-Anfrage, in der mehrere Bedingungen über... Dieses Thema im Forum "Microsoft Excel Hilfe" wurde erstellt von chris77, 8. Juli 2020.

  1. VBA SQL-Anfrage mit Array


    Hallo Zusammen

    Ich bin mal wieder auf ein Problem gestoßen, bei dem ich nicht weiterkomme. In VBA eine SQL-Anfrage, in der mehrere Bedingungen über ein Array eingepflegt werden sollen.

    Ich habe folgenden SQL-Anfrage:

    Query = "SELECT tbl_Leben, tbl_Stadt, tbl_Land, tbl_Fluss WHERE((tbl_Haustier) NOT LIKE "Maus");"

    Jetzt soll es aber eine Auswahlmöglichkeit geben. Es soll nicht nur Maus ausgeschlossen werden, sondern Hund, Katze oder Maus. Dabei kann es aber auch sein, dass keine der Möglichkeiten ausgeschlossen wird, eine, 2 oder alle 3 Möglichkeiten.

    Ich dachte mir das wäre mit einem Array möglich. Aber es scheitert schon in den Ansätzen, da ich immer nur eine Möglichkeit ausschließen kann.

    Vielen Dank für eure Hilfe!

    Grüße
    Chirs
     
    chris77, 8. Juli 2020
    #1
  2. Exl121150 Erfahrener User
    Hallo,

    eine Auswahlliste wird in SQL mittels des IN-Operators spezifiziert, also:

    Code:
    Query = "SELECT tbl_Leben, tbl_Stadt, tbl_Land, tbl_Fluss WHERE tbl_Haustier NOT IN ('Maus','Hund','Katze');"
    Anmerkungen:
    1) Der IN-Operator benötigt zwingend die beiden runden Klammern - also auch dann, wenn es nur 1 Aufzählungselement gibt.
    2) Wenn du der String-Variable "Query" eine SQL-Abfrage zuweist, so musst du, wenn in dieser Stringvariablen Stringkonstanten wie zB. "Maus" oder "Hund" vorkommen, die Anführungszeichen verdoppeln: also zB. Query = "SELECT tbl_Leben, .... WHERE tbl_Haustier NOT IN (""Maus"",""Hund"");"
    Häufig darf man aber in SQL auch einfache Anführungszeichen (sogenannte Hochkommata) verwenden, sodass sich dann die Schreibweise und auch die Lesbarkeit erheblich vereinfacht so wie bei meiner Schreibe im CODE-Fenster vorhin.
     
    Exl121150, 8. Juli 2020
    #2
  3. Hallo Anton,

    vielen Dank für deine Hilfe!

    Nach einigem Probieren habe ich es hingekriegt. Auch mit Arrays konnte ich NOT IN umsetzten.

    Zum Schluss fehlt mir noch ein Platzhalter.

    Hier ein Beispiel das funktioniert:

    TestString(0) = "Hund"
    TestString(1) = "Katze"
    TestString(2) = "Maus"

    Query = "SELECT tbl_Leben, tbl_Stadt, tbl_Land, tbl_Fluss WHERE tbl_Haustier NOT IN ('" & TestString(0) & "','" & TestString(1) & "','" & TestString(2) & "');"

    Wie schaffe ich es jetzt z.B. "Haushund", "kleine Maus" oder "Katzen" mit ein- bzw ausschließe? Ich wollte das ganz normal über den "*" Platzhalter machen. Leider ohne passendes Ergebnis.

    Zum Beispiel bring TestString(0) = "*Hund*" nicht das gewünschte Ergebnis.

    Hast du noch einen Tip für mich?

    Vielen Dank
    Chris
     
    Zuletzt bearbeitet: 8. Juli 2020
    chris77, 8. Juli 2020
    #3
  4. Exl121150 Erfahrener User

    VBA SQL-Anfrage mit Array

    Hallo,

    folgenden VBA-Code in ein allgemeines Codemodul kopieren:
    Code:
    Option Explicit
    
    Public Function InListe(Feld As Variant, ParamArray Liste()) As Boolean
      Dim Posten As Variant
      Dim strFeld As String
      
      InListe = False
      strFeld = Nz1(Feld, "")
      For Each Posten In Liste
        If strFeld Like Posten Then InListe = True: Exit For
      Next Posten
    End Function
    
    Public Function NichtInListe(Feld As Variant, ParamArray Liste()) As Boolean
      Dim Posten As Variant
      Dim strFeld As String
      
      NichtInListe = True
      strFeld = Nz1(Feld, "")
      For Each Posten In Liste
        If strFeld Like Posten Then NichtInListe = False: Exit For
      Next Posten
    End Function
    
    Private Function Nz1(Feld As Variant, Optional WrtIfNull As Variant) As String
      If IsNull(Feld) Then
        If IsEmpty(WrtIfNull) Then
          Nz1 = ""
        Else
          Nz1 = WrtIfNull
        End If
      Else
        Nz1 = Feld
      End If
    End Function
    
    dann lautet der SQL-Select-Befehl:
    Code:
    Query = "SELECT tbl_Leben, tbl_Stadt, tbl_Land, tbl_Fluss WHERE NichtInListe(tbl_Haustier, '[COLOR="#FF0000"][B]*[/B][/COLOR]Hund[COLOR="#FF0000"][B]*[/B][/COLOR]','Katze','[COLOR="#FF0000"][B]*[/B][/COLOR]Maus');"
    bzw.
    Code:
    Query = "SELECT tbl_Leben, tbl_Stadt, tbl_Land, tbl_Fluss WHERE InListe(tbl_Haustier, '[COLOR="#FF0000"][B]*[/B][/COLOR]Hund[COLOR="#FF0000"][B]*[/B][/COLOR]','Katze','[COLOR="#FF0000"][B]*[/B][/COLOR]Maus');"
    anstelle des naturgemäß nicht funktionierenden NOT IN(...)
    Query = "SELECT tbl_Leben, tbl_Stadt, tbl_Land, tbl_Fluss WHERE tbl_Haustier NOT IN('*Hund*','Katze','*Maus');"
    bzw. IN(...)
    Query = "SELECT tbl_Leben, tbl_Stadt, tbl_Land, tbl_Fluss WHERE tbl_Haustier IN('*Hund*','Katze','*Maus');"

    Dabei sind jetzt in 'Hund', 'Katze', ... alle Match-Codes erlaubt, die auch im VBA-Operator LIKE erlaubt sind.
     
    Exl121150, 8. Juli 2020
    #4
  5. Super! Dank Dir Anton. Das werd ich am Wochenende ausprobieren. Vorher komme ich leider nicht dazu.

    Viele Grüße
    Chris
     
    chris77, 9. Juli 2020
    #5
  6. Hallo Anton,

    ich hab´s grad ausprobiert und folgende Fehlermeldung bekommen.

    VBA SQL-Anfrage mit Array hundkatzemaus.JPG

    Mein Wissen in der Hinsicht ist nicht so groß, aber könnte es sein, dass in dem String die Funktion als solche nicht erkannt wird? Hab zwar dran rumgebastellt, aber nichts funktionierendes hinbekommen. Kannst du mir nochmal weiterhelfen?

    Vielen Dank

    Grüße
    Chris
     
    chris77, 10. Juli 2020
    #6
  7. Sorry, hatte nen Fehler drin.

    Leider kommt jetzt auch wieder eine Fehlermeldung. Die Funktionen von dir hab ich aber in ein allgemeines Modul gepackt.

    VBA SQL-Anfrage mit Array hundkatzemaus.JPG
     
    chris77, 10. Juli 2020
    #7
  8. Exl121150 Erfahrener User

    VBA SQL-Anfrage mit Array

    Hallo,

    stimmt es, dass du in Excel eine SQL-SELECT-Abfrage erstellst und diese dann in die String-Variable "Query" speicherst?
    Dann wirst du sicherlich den Inhalt dieser Variablen an ein Datenbankprogramm (ACCESS oder SQL-SERVER oder ORACLE oder ...) übergeben, damit die Abfrage dort ausgeführt wird und du in Excel die Abfrageantwort erhältst.

    Wenn diese meine Vermutung zutreffen sollte, muss die benutzerdefinierte Funktion 'NichtInListe' (bzw. auch gegebenenfalls auch 'InListe' bzw. 'Nz') im Datenbanksystem des Zielrechners verfügbar sein, denn dort wird ja dann die Abfrage ausgeführt.
    Handelt es sich um ACCESS von Microsoft, dann kannst du die Funktion 'Nz', die als private-deklarierte Funktion bei mir in den Makros enthalten war, weglassen. Diese ist nämlich von Haus aus in ACCESS enthalten und somit auch in den ACCESS-SQL-Dialekten verfügbar.
     
    Exl121150, 14. Juli 2020
    #8
  9. Hallo Anton,

    danke für eine Antwort.
    Ja, ich übergebe einen Querystring und spreche Access über ADO an. Leider verstehe ich nicht ganz was ich genau machen soll. Also im Klartext weiß ich nicht wie ich vorgehen soll.

    Grüße
    Chris
     
    chris77, 20. Juli 2020
    #9
  10. Exl121150 Erfahrener User
    Hallo,

    Du erstellst in EXCEL mithilfe eines VBA-Makros eine SQL-SELECT-Anweisung, die du an ACCESS übergibst, damit ACCESS diese Abfrage ausführt und dich anschließend in Excel mit der Abfrageantwort versorgt. In dieser Abfrage verwendest du aber eine benutzerdefiniert Funktion (namens "NichtInListe(...)" bzw. "InListe(...)").
    Damit ACCESS die Abfrage ausführen kann, muss die Access-Anwendung diese benutzerdefinierte Funktion auch kennen - nicht bloß Excel - in Excel ist sie dann eigentlich überflüssig.

    Du musst also diese benutzerdefinierte Funktion als Makro in die ACCESS-Datenbankanwendung einfügen - in ähnlicher Weise, wie man dies auch in Excel tut:
    zB. folgenden VBA-Code kopieren und in der ACCESS-Anwendung in ein allgemeines Codemodul einfügen - und sie dort als PUBLIC deklarieren, was ohnehin im folgenden Code schon geschehen ist:
    Code:
    Option Explicit
    Option Compare Text
    
    [COLOR="#FF0000"]Public [/COLOR]Function InListe(Feld As Variant, ParamArray Liste()) As Boolean
      Dim Posten As Variant
      Dim strFeld As String
      
      InListe = False
      strFeld = [COLOR="#FF0000"]Nz[/COLOR](Feld, "")
      For Each Posten In Liste
        If strFeld Like Posten Then InListe = True: Exit For
      Next Posten
    End Function
    
    [COLOR="#FF0000"]Public [/COLOR]Function NichtInListe(Feld As Variant, ParamArray Liste()) As Boolean
      Dim Posten As Variant
      Dim strFeld As String
      
      NichtInListe = True
      strFeld = [COLOR="#FF0000"]Nz[/COLOR](Feld, "")
      For Each Posten In Liste
        If strFeld Like Posten Then NichtInListe = False: Exit For
      Next Posten
    End Function
    Da ACCESS die Funktion Nz(..) standardmäßig kennt, benötigst du sie daher dort nicht. Ich habe daher die Nz1(..)-Funktionsaufrufe des Excel-Makros umbenannt in Nz(...) und die Deklaration "Private Function Nz1(...) .... End Function" entfernt.
     
    Exl121150, 20. Juli 2020
    #10
  11. Ahhh...ich denk ich hab´s verstanden!

    Vielen Dank Anton. Ich werd´s ausprobieren und Bescheid geben.

    Grüße Chris
     
    chris77, 21. Juli 2020
    #11
  12. Nochmal vielen Dank Anton.

    Nach einigem Tüfteln hab ich es hinbekommen.

    Grüße
    Chris
     
    chris77, 30. Juli 2020
    #12
Thema:

VBA SQL-Anfrage mit Array

Die Seite wird geladen...
  1. VBA SQL-Anfrage mit Array - Similar Threads - VBA SQL Anfrage

  2. Binäre Dateien verarbeiten

    in Microsoft Access Hilfe
    Binäre Dateien verarbeiten: Hallo zusammen, nachdem ich weder auf deutsch- noch englischsprachigen Foren fündig werde, bitte ich Euch um Unterstützung. Ich habe eine PostgreSQL-Datenbank, darin Bytea-Felder gefüllt mit...
  3. Binäre Dateien verarbeiten

    in Sonstiges
    Binäre Dateien verarbeiten: Hallo zusammen, nachdem ich weder auf deutsch- noch englischsprachigen Foren fündig werde, bitte ich Euch um Unterstützung. Ich habe eine PostgreSQL-Datenbank, darin Bytea-Felder gefüllt mit...
  4. per suchfeld adressdaten aud sql datenbank einfügen

    in Microsoft Word Hilfe
    per suchfeld adressdaten aud sql datenbank einfügen: Hallo, ich würde gern ein Suchfeld in Word 2016 einfügen das im SQL Server eine Tabelle nach dem eingegebenen Kriterium -> Kundennummer durchsucht und dann das vorgegebene Adressformat in word...
  5. Backup SQL Server Datenbank VBA

    in Microsoft Access Hilfe
    Backup SQL Server Datenbank VBA: Hallo an alle! Ich bastle gerade an einem Modul für eine Datensicherung für den MS SQL-Server aus einem MS Access Frontend heraus. Irgendwie habe ich einen Knoten, ich schaffe es nicht den String...
  6. MS-SQL Tabelle per ODBC mit VBA verknüpfen

    in Microsoft Access Hilfe
    MS-SQL Tabelle per ODBC mit VBA verknüpfen: Hallo zusammen, bisher verwende ich in Access 2013 / 2016 die Funktion "Externe Daten" -> "ODBC-Datenbank" um Tabellen aus einer MS-SQL Datenbank zu verknüpfen. Das funktioniert soweit bestens....
  7. VBA Excel-Datenimport per SQL

    in Microsoft Access Hilfe
    VBA Excel-Datenimport per SQL: Guten Tag, ich habe eine Tabelle mit Artikelnr, Umsatz, Preis. Es sind 50000 Artikelnummern vorhanden, die jeweiligen Umsätze und Preise muss ich aus einer anderen Datei herholen. Meine Idee...
  8. ID aus SQL Recordset ermitteln - Geänderter Treiber?

    in Microsoft Access Hilfe
    ID aus SQL Recordset ermitteln - Geänderter Treiber?: ch habe ein akutes Problem beim Zugriff auf den SQL Server. Seit vielen Jahren nutze ich folgendes Prinzip. Im VBA lege ich einen neuen Datensatz in einem Recordset an. Da der SQL Server die...
  1. Diese Seite verwendet Cookies, um Inhalte zu personalisieren, diese deiner Erfahrung anzupassen und dich nach der Registrierung angemeldet zu halten.
    Auf dieser Website werden Cookies für die Zugriffsanalyse und Anzeigenmessung verwendet.
    Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden