Query Comma Delemited Textbox

[Drum2001]

I have a database where I need to query multiple items. Is it possible
to run a query based on a textbox where the information is delimited by
a comma.

Example:

Show me all names where Social Security Number:

[TEXTBOX] = 111223333, 444556666, 777889999

It seems to me a loop of some sort is needed.

Any suggestions would be great. Thanks!

[Brian Puffer]

Drum,

You would need a loop to parse the values from the textbox...somet hing
like this:

Dim lNew as Boolean
Dim str as String
Dim aVals()
Dim idx as Integer

idx = 0
ReDim aVals(idx)

for i = 1 to len(TextBox)
If Mid(TextBox, i, 1) = "," then
lNew = True
End If

If Not lNew then
str = str & Mid(TextBox, i, 1)
Else
aVals(idx) = str
str = ""
idx = idx + 1
ReDim Preserve aVals(idx)
i = i + 1
End If
Next i

After parsing the values into an array, use the array to build your
query...

Hope this helps,
Brian
Drum2001 wrote:

I have a database where I need to query multiple items. Is it possible
to run a query based on a textbox where the information is delimited by
a comma.

Example:

Show me all names where Social Security Number:

[TEXTBOX] = 111223333, 444556666, 777889999

It seems to me a loop of some sort is needed.

Any suggestions would be great. Thanks!

[Drum2001]

Brian,

Thank you for the help.

Would you be able to elaborate more? I am rather new to MS Access.

Brian Puffer wrote:

Drum,

You would need a loop to parse the values from the textbox...somet hing
like this:

Dim lNew as Boolean
Dim str as String
Dim aVals()
Dim idx as Integer

idx = 0
ReDim aVals(idx)

for i = 1 to len(TextBox)
If Mid(TextBox, i, 1) = "," then
lNew = True
End If

If Not lNew then
str = str & Mid(TextBox, i, 1)
Else
aVals(idx) = str
str = ""
idx = idx + 1
ReDim Preserve aVals(idx)
i = i + 1
End If
Next i

After parsing the values into an array, use the array to build your
query...

Hope this helps,
Brian
Drum2001 wrote:

I have a database where I need to query multiple items. Is it possible
to run a query based on a textbox where the information is delimited by
a comma.

Example:

Show me all names where Social Security Number:

[TEXTBOX] = 111223333, 444556666, 777889999

It seems to me a loop of some sort is needed.

Any suggestions would be great. Thanks!

[pietlinden]

Drum2001 wrote:

I have a database where I need to query multiple items. Is it possible
to run a query based on a textbox where the information is delimited by
a comma.

Example:

Show me all names where Social Security Number:

[TEXTBOX] = 111223333, 444556666, 777889999

It seems to me a loop of some sort is needed.

Any suggestions would be great. Thanks!

I would use several textboxes if you can... otherwise, you'd have to
parse them all (you could use Split), but then passing the result of
the function to a query where you build the In([list]) is going to be
ugly. If you use unbound textboxes, then you can easily just create
your own IN statement. or OR statement.

[Drum2001]

I thought of the multiple textboxes.. though, we are looking at typing
50+ IDs at a time.

The textboxes are unbound, though...
pi********@hotm ail.com wrote:

Drum2001 wrote:

I have a database where I need to query multiple items. Is it possible
to run a query based on a textbox where the information is delimited by
a comma.

Example:

Show me all names where Social Security Number:

[TEXTBOX] = 111223333, 444556666, 777889999

It seems to me a loop of some sort is needed.

Any suggestions would be great. Thanks!

I would use several textboxes if you can... otherwise, you'd have to
parse them all (you could use Split), but then passing the result of
the function to a query where you build the In([list]) is going to be
ugly. If you use unbound textboxes, then you can easily just create
your own IN statement. or OR statement.

[deluxeinformation]

Drum2001 wrote:

I have a database where I need to query multiple items. Is it possible
to run a query based on a textbox where the information is delimited by
a comma.

Example:

Show me all names where Social Security Number:

[TEXTBOX] = 111223333, 444556666, 777889999

It seems to me a loop of some sort is needed.

Any suggestions would be great. Thanks!

You shouldn't need to do the extensive parsing that others have
suggested if your SSN field is numeric. For example, if your table was
called tblPeople and your textbox was called txtSSN and you wanted to
select all records with SSN's matching the ones listed in your textbox
you could create a query string as in the following example:

dim strSQL as string

strSQL = "select * from tblPeople where SSN in (" & txtSSN & ")"

HTH,
Bruce

[Kc-Mass]

If you are processing 50 people per record I would guess that you are not
going against
an MS Access db. Maybe the SSA db to validate SSNs?

What I think you need to think about is how you are going to receive,
evaluate and store
the results of the query. The data is going to get parsed. The only
questions are when, by what method and on what machine. Big iron can do the
parsing very efficiently but if you query 50 at a time then someone at your
end has to go back and figure out what the results were vis a vis the
request and the returned data and in the mean time several other iterations
of another 50 and another 50 have gone and returned more data.

If the data coming back is ordered by individual SSN and you have 50 SSNs in
a string (for each record) good luck matching it up. That's where Clerks
used to get jobs, comparing output documents to input documents.

Getting feedback on 50 SSNs would mean that you would again have to parse
the string to determine what your results were at the individual SSN level
which is probably your level of interest.

If it were mine I would parse the data to fields of single SSNs. You could
then build whatever string you want to issue a query. If you have someone
type 50 SSNs with comma delimiters into a field, how do you reliably know
which SSNs you have been accurately queried?

<de************ ***@gmail.comwr ote in message
news:11******** **************@ i42g2000cwa.goo glegroups.com.. .

>
Drum2001 wrote:

>I have a database where I need to query multiple items. Is it possible
to run a query based on a textbox where the information is delimited by
a comma.

Example:

Show me all names where Social Security Number:

[TEXTBOX] = 111223333, 444556666, 777889999

It seems to me a loop of some sort is needed.

Any suggestions would be great. Thanks!

You shouldn't need to do the extensive parsing that others have
suggested if your SSN field is numeric. For example, if your table was
called tblPeople and your textbox was called txtSSN and you wanted to
select all records with SSN's matching the ones listed in your textbox
you could create a query string as in the following example:

dim strSQL as string

strSQL = "select * from tblPeople where SSN in (" & txtSSN & ")"

HTH,
Bruce