473,324 Members | 1,856 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Query Comma Delemited Textbox

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!

Oct 17 '06 #1
6 2079
Drum,

You would need a loop to parse the values from the textbox...something
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!
Oct 17 '06 #2
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...something
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!
Oct 17 '06 #3

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.

Oct 17 '06 #4
I thought of the multiple textboxes.. though, we are looking at typing
50+ IDs at a time.

The textboxes are unbound, though...
pi********@hotmail.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.
Oct 17 '06 #5

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

Oct 17 '06 #6
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.comwrote in message
news:11**********************@i42g2000cwa.googlegr oups.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

Oct 18 '06 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Keith Doran | last post by:
I have a webform page that contains a textbox control. The textbox is initialized by the codebehind module and contains a value when the page is first loaded. The user can then edit the textbox...
11
by: Daniel Seipel | last post by:
I'm new at ASP and I'm trying to query an MS SQL database (Northwind). I want the page to be setup with a textbox where the user inputs a list of comma separated numbers and the query would be...
6
by: jjturon | last post by:
Can anyone help me?? I am trying to pass a Select Query variable to a table using Dlookup and return the value to same select query but to another field. Ex. SalesManID ...
2
by: fong.yang | last post by:
Need help setting a select query where person enters a list of zipcodes to lookup. I want to prompt the user to enter all the zip codes, then run the query against to table to pull all records....
19
by: M. Posseth | last post by:
Hi ,, i live in Europe in Europe the decimal seperator is , not a . why is it so that if i press on my numeric keypad the . a . appears ?? in my opinion it should be a , ( sounds odd but...
1
by: Intrepid_Yellow | last post by:
Hi, I have the following code that runs my report generator. The user selects a table from a combo box, then whatever fields they want from a list box. (This part all works and the report runs...
3
by: Phil Stanton | last post by:
I have a number of queries which use code for the output of 1 or more fields. For example Address:GetAddress(AddressID, True, 60) Address ID Points to an Address in a table - Address Line1, Line...
7
by: Joza | last post by:
Hi! How to replace dot with comma when user is typing somewhat in textBox? I have wrote this part of code to detect typed dot, but how to replace it with coma: private void...
3
by: premprakashbhati | last post by:
sir, how to put dynamic comma after 12 characters in textbox means 13th char is comma.... can u plz suggest me ...how i should do it... thanx... prem prakash...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.