SQL WHERE clause in VBA: Use array-variable

Question

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?

Gordon Linoff · Accepted Answer · 2016-08-01 01:11:38Z

1

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

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

edited Aug 1, 2016 at 1:11

answered Jul 30, 2016 at 13:21

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Gordon Linoff Over a year ago

@tmenke . . . Does this refer to the construction of the string or to the SQL call itself?

tmenke Over a year ago

Hi Gordon, thanks for your swift Response. If I try it like that: recordset2.Open "SELECT * FROM [Table] WHERE [Name] IN (' " & Join(varRecord, "', ' ") & ")' ". I get the error: Invalid procedure call or argument (Error 5).

tmenke Over a year ago

it refers to the SQL Call itself

Gordon Linoff Over a year ago

@tmenke . . . Then you should edit the question and show what the string looks like after it is constructed.

tmenke Over a year ago

that's the concrete SQL-Statement:

slide7_netset_basics.Open "SELECT Class FROM [NetSet_Basics] WHERE [LoadingID] =" & LId & "And [NettingSet] IN (' " & Join(varRecord, "', ' ") & ")' "

|

Collectives™ on Stack Overflow

SQL WHERE clause in VBA: Use array-variable

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related