Using an array to query a table

[girlpower]

I have a client who wants to be able to type a comma-delimited string in a text box and use that string to query against a table. I'm not sure how to achieve this. My thinking is to parse the string into an array -- and then use the array as a series of values that can be used as criteria in a select query. If this is a good (and best!) way of setting multiple criteria in a query, could someone help me with the VBA?

Thanks a bunch!

 

[gol4]

I just happen to have been working on refining something like this. It is still in the rough stages but here goes.
It uses space as the delimiter but you could easily make it take commas. also uses OR rather then AND.
look it over and if you have questions let me know.

I am including 2 functions the one that builds the SQL and the other one is basically the replace function

These functions are to provide an example of 1 of many possible ways to accomplish what is described above and include no error trapping etc.. so as to make the code as easy to understand as possible

Use like this
Private Sub Text0_AfterUpdate()
Me.List1.RowSource = DSource("[fieldname]", "tablename", "textboxname", "[field1], [field2]&quot
End Sub

Public Function DSource(FldNam As String, TblNam As String, SrchNam As String, Optional AddFld As String) As String
'builds sql that can be used as a record source using string from a text box
'use similar to dlookup() but gets criteria from text box
'fldname name of field to search
'Tblnam name of table that contains the field to search
'srchNam name of control that contains search string
'pass separated by a space (test this)
'addfld additional fields to return(optional) separated by a comma and a space fieldnames 'with a space must be enclosed in brackets ("field1, field2, [field 3]"

Dim strfilter As String
strfilter = BuildCriteria(FldNam, 7, "*" & SReplace(Me(SrchNam), " ", "* or *&quot & "*&quot
DSource = "SELECT " & FldNam
DSource = DSource & IIf(AddFld = "", "", ", " & AddFld)
DSource = DSource & " FROM " & TblNam & " WHERE " & strfilter
Me.Text4.Value = Dsource

Public Function SReplace(StrOrig As String, Optional StrOld As String, Optional StrNew As String) As String
'performs something like excel replace function
Dim i As Integer
SReplace = ""
i = 1
While i <= Len(StrOrig)
SReplace = SReplace & IIf(Mid(StrOrig, i, 1) = StrOld, StrNew, Mid(StrOrig, i, 1))
i = i + 1
Wend
End Function

 

[gol4]

Sorry I messed up the pasting of the d source function deled the wrong line should read like this (without the comments)

Public Function DSource(FldNam As String, TblNam As String, SrchNam As String, Optional AddFld As String) As String

Dim strfilter As String
strfilter = BuildCriteria(FldNam, 7, &quot;*&quot; & SReplace(Me(SrchNam), &quot; &quot;, &quot;* or *&quot & &quot;*&quot
DSource = &quot;SELECT &quot; & FldNam
DSource = DSource & IIf(AddFld = &quot;&quot;, &quot;&quot;, &quot;, &quot; & AddFld)
DSource = DSource & &quot; FROM &quot; & TblNam & &quot; WHERE &quot; & strfilter
End Function

 

[girlpower]

I'm not sure if this will work for me. This code looks like it will programmatically fill a list box by which I can then run a query -- I don't want to create another form to run a search.

The user would type a comma-delimited string in a text box. Code would then parse the string into an array. The array would then be used as criteria in a parameter query. So if the user types &quot;23, 45, 78&quot; (those values being customer numbers), he'll get 3 records returned. I'm not sure if a parameter query would even allow me to use arrays. If not, I suppose I could create a search form, but that would extend the project past its current scope. So you see my dilemma.

If I'm mistaken in understandng how your code works, could you explain to me how you use it. Thanks for helping me out!

 

[gol4]

The code uses buildcriteria to assist in building the SQL statement when searching a text box

your are 100% correct in that usually you will need to parse the string because if I try passing to a query as my criteria
&quot;*&quot; & forms!formname!fieldname &quot;*&quot; it looks for a literal

so if I pass this or that it looks for
&quot;this or that&quot; rather then &quot;this&quot; or &quot;that&quot;

if you want pass numbers then all you probably need to do is build your criteria using the IN operator
where fieldname in (23, 45, 78)

&quot;where fieldname in (&quot; forms!formname!fieldname &&quot; )&quot;

the purpose of the function is if you were to search on multiple text the critera needs to read

where fieldname like &quot;*text1*&quot; or fieldname like &quot;*text2*&quot;

as you can see much more difficult to construct the string.
dsource builds that string into an sql statement that then is used for the recordsource. so far I have successfully used it on querydefs,reports,Listboxes and comboboxes
hope that clears it up.

 

[tomschafer]

An easier way would be to use a single SQL like this...

select
*
from
Table
where
instr(FieldName, TextBox)>0