Hallo,
In einem VBA Code zu einem Form Control möchte ich in einem Sql Kommando den Befehl:
instr(table.feld1, my_variable) = 1
verwenden.
Er kann aber my_variable nicht auflösen, obwohl sie im Programm deklariert ist und ein String-Text zugewiesen wurde.
Wer weiss eine Lösung?
Vielen Dank HPS
Schau dir Mal mein SQL-String Tutorial an.
https://codekabinett.com/rdumps.php?Lang=1&targetDoc=vba-sql-string-tutorial
Darin findest du, zumindest indirekt, die Antwort auf diese Frage. Viel wichtiger aber, auch die Grundlagen dazu.
Dein SQL String weiß nichts von der deklarierten Variable.
Zur weiteren Klärung solltest du mindestens auch deinen SQL-String zeigen, den du gebastelt hast.
Dein SQL String weiß nichts von der deklarierten Variable.
Zur weiteren Klärung solltest du mindestens auch deinen SQL-String zeigen, den du gebastelt hast.
Hier mal ein Code, so wie ich deine Problemstellung verstanden habe.
Public Sub test()
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Set qdf = CurrentDb.QueryDefs("A1")
qdf.Parameters(0) = "k"
Set rs = qdf.OpenRecordset
MsgBox rs(0)
End Sub
'SELECT InStr([Feld1],[my_variable])=1 FROM T1;
Wobei der Code
instr(table.feld1, my_variable) = 1erstmal in einem Select so nicht viel Sinn ergibt. Hier wäre dann schon zu hinterfragen, was eigentlich gewünscht wird.
Knobbi38
Hier das Code-Beispiel:
Dim Str_Ort as string
Dim SQLStr as string
'Ziel: Es sollen nur Datensätze mit "Passau" oder Anfangsteile dieses Strings eingefügt werden.
Str_Ort = "Passau" 'alternativ Str_Ort = "Pa"
SQLstr = "insert into KOST_ERW Select KOST.* from KOST where instr(KOST.ORT, STR_Ort) = 1;"
funktioniert nicht, ebenso nicht:
SQLstr = "insert into KOST_ERW Select KOST.* from KOST where instr(KOST.ORT, " & STR_Ort & ") = 1;"
Letzteres sollte laut Antwort #1 eigentlich funktionieren...
VG HPS
Hi,
ich habe mal Chatgpt bemüht, die Antwort find ich gut erklärt:
In Access-SQL (also Jet/ACE SQL) funktioniert InStr() zwar, aber nur mit konkreten Werten oder Strings, nicht direkt mit VBA-Variablen innerhalb des SQL-Textes.
Wenn du schreibst:
SQLstr = "... where instr(KOST.ORT, STR_Ort) = 1;"
→ Dann denkt Access, dass STR_Ort eine Feldname ist, nicht deine VBA-Variable.
Daher kommt kein Treffer.
Du musst den Wert der VBA-Variable als Textliteral in den SQL-String einsetzen — also in einfache Hochkommas ' ' einbetten.
So geht's richtig:
Dim Str_Ort As String
Dim SQLstr As String
Str_Ort = "Passau" ' oder "Pa"
SQLstr = "INSERT INTO KOST_ERW " & _
"SELECT KOST.* FROM KOST " & _
"WHERE InStr(KOST.ORT, '" & Str_Ort & "') = 1;
Falls "Passau" immer an Anfang steht, könnte man noch einfacher mit "Like" arbeiten.
Martin
Hallo,
anstatt mit Like oder Instr() = 1 könnte man gleich einfach Left() verwenden.
Stellt sich nur die Frage, wofür so eine Kopieraktion notwendig ist und warum die Tabelle nicht normalisiert ist, denn dann wäre so eine Krücke nicht notwendig.
Knobbi38
Zitat von: Hpseel am November 09, 2025, 22:17:12SQLstr = "insert into KOST_ERW Select KOST.* from KOST where instr(KOST.ORT, " & STR_Ort & ") = 1;"
Letzteres sollte laut Antwort #1 eigentlich funktionieren...
Du hast ein Detail übersehen. Zeichenfolgen müssen in Begrenzer eingeschlossen werden.
D.h.:
SQLstr = "insert into KOST_ERW Select KOST.* from KOST where instr(KOST.ORT, '" & STR_Ort & "') = 1;"Vor und hinter den ", die den String in VBA begrenzen, ist jeweils noch ein ', um den verketteten Text-Wert in dem resultierenden SQL-String zu begrenzen.
Ich würde hier auf das INSTR verzichten und stattdessen LIKE verwenden, weil das unmittelbar Teil des SQL-Standards ist und keine externe Funktion benötigt.
SQLstr = "insert into KOST_ERW Select KOST.* from KOST where KOST.ORT LIKE '*" & STR_Ort & "*';"Zitat von: Knobbi38 am November 10, 2025, 11:05:26anstatt mit Like oder Instr() = 1 könnte man gleich einfach Left() verwenden.
Reines SQL (LIKE) ohne Funktionsaufruf (Left oder Instr) dürfte effizienter sein.
Hallo. vielen Dank für die Tipps!
>> "Like" liefert mir zu viele Ergebnisse, wenn nur wenige Buchstaben als Suchstring eingegeben werden.
Die anderen Vorschläge haben bei mir (Access 2016) nicht funktioniert.
Es wird wohl schon so sein, dass das VBA Programm den SQL-String an den Parser der Jet-Engine übergibt und dieser den Variablenbezug nicht auflösen kann ...
Ich habe mich gefragt, was er denn auflösen kann und bin auf eine Lösung mit TempVars gekommen:
Im Code:
TempVars.Add "Ort", my_Var ' also Zuweisung meiner Variablen an eine TempVar
...
SQLStr = " Insert ......where instr(KOST.Ort, TempVars![Ort] = 1;"
Dieser "Umweg" über TempVars funktioniert und müsste auch bei anderen eingebauten Funktionen gehen.
Vielen Dank nochmal für die Tipps und weiter frohes Schaffen ! (Programmieren !)
HPS
@Hpseel Deiner Argumentation mit Like kann ich nicht folgen. Instr() liefert doch exakt die gleiche Anzahl DS - dann doch besser Like().
"TempVars.Add "Ort", my_Var ' also Zuweisung meiner Variablen an eine TempVar"
Damit wird der Tempvars Auflistung eine neues Tempvar-Objekt zugewiesen. Bei einer wiederholten Zuweisung wird zwar kein Fehler gemeldet, aber eigentlich wird für eine Zuweisung die Value Eigenschaft verwendet. Das wäre dann konsequent und besser lesbar.
Unabhängig davon sollten Eingaben nicht direkt ungeprüft verwendet werden. Besser ist es, dafür eine Parameterabfrage mit Like zu nutzen.
Knobbi38
Hallo Knobbi,
du hast sicher recht, LIKE entspricht mehr dem SQL Gedanken.
Ich bin wohl nach mehr als 30 Jahren Excel VBA Programmierung noch recht mit der "instr" Funktion verwachsen ...;-) Vermutlich habe ich sie einige tausend mal verwendet...
Einen kleinen Unterschied gibt es wohl: Bei LIKE muss noch der * hinten drangesetzt werden, bei instr genügt es, einfach die Buchstaben zu nehmen, die ab Position 1 erscheinen sollen, oder die bei instr(...) <> 0 irgendwo im Prüfstring vorkommen.
Meine ursprüngliche Frage "VBA Variablen im SQL String" ist glaube ich mit dem TempVar Ansatz zufriedenstellend gelöst. Du hast recht, wenn die Prozedur mehrfach durchlaufen wird, sollte man am Sub-Ende die TempVariable wieder löschen.
VG HPS
Hier mal ein Lösungsvorschlag mit einer Parameterquery:
SQL für die Parameterquery "qryAppend":
PARAMETERS pPattern Text ( 255 );
INSERT INTO tblDataNeu ( Vorname, Nachname )
SELECT Vorname, Nachname
FROM tblData
WHERE Nachname Like [pPattern];
und dann der VBA Code zum aufrufen:
Sub AppendRecords()
Dim qdf As QueryDef
Set qdf = DBEngine(0)(0).QueryDefs("qryAppend")
qdf.Parameters("pPattern") = "A*"
qdf.Execute dbFailOnError
Debug.Print qdf.RecordsAffected & " Datensätze angefügt."
qdf.Close : Set qdf = Nothing
End Sub
Knobbi38
Prima Knobbi!
Habe es ausprobiert und es funktioniert.
Dann gibt es also (mindestens) 2 Lösungsansätze!
VG HPS
ZitatDann gibt es also (mindestens) 2 Lösungsansätze!
Ich kenne noch einen dritten, - öffentliche Property/Function statt TempVar.
Hallo,
...übrigens nach so vielen Jahren VBA: Like gibt es auch in VBA und ist auch da oft sehr viel einfacher als Instr... ;)
Gruß
Christian