Using array in WHERE clause of SQL statement using access VBA

Question

I have an array ListBoxContents(), it will contain the items like '15', '16','25'..upto 10 items. I'm trying to retrieve data in the column Bnumber where data of length >6 and starting with('15', '16','25'...) i.e those items specified in listbox .And trying to query these listbox items in where cluase of the sql statement

Table column Bnumber contains

 Bnumber
152
156
1523
16417
AA454
CC654
18A16
1826
18A16
25A76
54A16
54235A68

My VBA code

Private Sub arraywhere()
Dim qry As String 
Dim Size As Integer
Size = Form_Input_From.lstdigits.ListCount - 1
ReDim ListBoxContents(0 To Size) As String
ReDim LContents(0 To 30) As String       
Dim m As Integer    
For m = 0 To Size
    ListBoxContents(m) = Form_Input_From.lstdigits.ItemData(m)
Next m  


For m = 0 To Size
     qry = "SELECT col1,col2,Bnumber " & _
    "FROM table WHERE (Len([table].[Bnumber]))>6) AND (Left
     ([table].[Bnumber],2))=(" & ListBoxContents(m) & ");"
Next m   

Debug.Print qry    

Application.CurrentDb.QueryDefs("[arrayqry]").sql = qry
DoCmd.OpenQuery "[arrayqry]"

End Sub

But my WHERE clause reads only last array item only. How do i specify array in where clause?

Tim Williams · Accepted Answer · 2014-04-25 20:22:25Z

6

Try something like

" ...  ([table].[Bnumber],2)) in ('" & Join(ListBoxContents,"','") & "');"

edited Apr 25, 2014 at 20:22

answered Apr 25, 2014 at 20:10

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

2 Comments

Dmitry Pavliv Over a year ago

Tim, as far as I know, syntax should be In ('12','22','33','15');, but Join gives me In ('12,22,33,15');

Tim Williams Over a year ago

@simoco - typing on my phone, so not up to my usual standard - see edit

Jeremy Cook · Accepted Answer · 2014-04-25 20:08:45Z

0

You are setting qry to a new statement with each iteration of your for loop. Instead you need to concatenate a string based on your list box contents that will look like ("x", "y", "z") and replace = with in.

Finish by setting your query once it will look similar to this:

qry = "SELECT col1,col2,Bnumber " & _
"FROM table WHERE (Len([table].[Bnumber]))>6) AND (Left
 ([table].[Bnumber],2)) in (" & commaSeperatedContents & ");"

Where commaSeperatedContents is a String that is like ("x", "y", "z") but of course has your values.

answered Apr 25, 2014 at 20:08

Jeremy Cook

22.4k11 gold badges74 silver badges78 bronze badges

Comments

Dmitry Pavliv · Accepted Answer · 2014-04-25 20:20:47Z

0

Try this one:

Dim inPart As String

For m = 0 To Size
    inPart = inPart & "'" & ListBoxContents(m) & "',"
Next m
inPart = Left(inPart, Len(inPart) - 1)

qry = "SELECT col1,col2,Bnumber " & _
   "FROM [table] WHERE Len([table].[Bnumber])>6 AND " & _
   "Left([table].[Bnumber],2) In (" & inPart & ");"
Debug.Print qry

CurrentDb.QueryDefs("[arrayqry]").SQL = qry
DoCmd.OpenQuery "arrayqry"

edited Apr 25, 2014 at 20:20

answered Apr 25, 2014 at 20:10

Dmitry Pavliv

35.9k13 gold badges84 silver badges85 bronze badges

Comments

Yawar · Accepted Answer · 2014-04-25 20:20:55Z

The list of items in your array actually seems to be coming from the Form_Import_From_PMT.lstdigits control. Is this control bound to a data source? If so, you can simply join your table to that data source with a join clause that specifies that only rows with Bnumber values starting with the digits in the joined table are to be selected:

select col1, col2, Bnumber
from table as t
inner join tblDigits as d
on left(t.Bnumber, 2) = d.Digits
where len(t.Bnumber) > 6

If the control is not bound to a data source, then bind it now (creating a new table tblDigits to hold the digits, as shown above), and you'll be able to use the above query.

In short, data binding is how you 'use an array in a where clause' in Access.

Collectives™ on Stack Overflow

Using array in WHERE clause of SQL statement using access VBA

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related