tmenke tmenke - 4 months ago 30
SQL Question

SQL WHERE clause in VBA: Use array-variable

How I can insert an array (stored in a variable) into the WHERE clause of a SQL statement in VBA?

recordset1.Open "SELECT * FROM [Table] WHERE [NettingSet] = '" & varRecord & "'"


Originial string is:

recordset1.Open "SELECT Class FROM [BCBS279_NetSet_Basics] WHERE [LoadingID] =" & LId & "And [NettingSet] = '" & varRecord & "'"


I get this error:


Run-time error '13': Type mismatch


Array:

Dim varRecord As Variant
varRecord = slide7_netset_info_ID.GetRows(10, , 0)


This array just contains strings:

Example_1, Example_2, Example_3, ..., Example_10


Any idea how to fix it?

Answer

You want to use IN in SQL and then construct the IN list using "join" in VBA:

". . . WHERE [Name] IN ('" & Join(varRecord, "', '") & "')"