CODEWITHSUNDEEP
ms-accessvba
vuyy1182
vuyy1182

Reputation: 1686

Using array in WHERE clause of SQL statement using access VBA

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?

Upvotes: 2

Views: 8234

Answers (4)

Tim Williams
Tim Williams

Reputation: 166351

Try something like

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

Upvotes: 6

Yawar
Yawar

Reputation: 11607

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.

Upvotes: 0

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

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"

Upvotes: 0

Jeremy Cook
Jeremy Cook

Reputation: 22063

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.

Upvotes: 0

Related Questions