Solved

Search from a comma seperated field

Posted on 2006-07-19
34
641 Views
Last Modified: 2008-03-10
How do I write a query that searches for results in a field that has several numbers seperated by commas.
My Access database has a field called comma_jobs that has values that look like this:

223,224,225,226, etc.
varjobs is a Request.form var that has comma seperated values like the example above.   How do I write the following to search paymaker for these value.   the values are ID's for the records in paymaker if that helps any.
SELECT *
FROM paymaker
WHERE userid = 'varjobs'
0
Comment
Question by:budgilbert67
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 9
  • 6
  • +3
34 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17143552
>>My Access database has a field called comma_jobs that has values that look like this:<<
Since the MS SQL Server dialect is significantly different to the MS Access SQL dialect you may have better luck in a more appropriate Topic Area such as:
http://www.experts-exchange.com/Databases/MS_Access/

Having said that I believe the function you are looking for is the JET function InStr()
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17143557
You can also use something like this:

Dim SQL
SQL = "SELECT * FROM paymaker WHERE userid In (" & varjobs & ")"
0
 

Author Comment

by:budgilbert67
ID: 17145364
How do I movie this question to MS Access?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:budgilbert67
ID: 17145366
How do I move this question to MS access?  Can't spell today
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17147255
See here from the EE Guidelines:

I put my question in the wrong topic area. Now what?
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi13
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 17149826
SELECT *
FROM paymaker
WHERE  instr(1, comma_jobs, varjobs) <> 0
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17149895
You'll be wanting to concat into a string then - as Anthony demonstrated earlier.
I'd suggest a slight variation using something close to either

"SELECT * FROM paymaker WHERE  ',' & [userid] & ',' Like '*," & varjobs & ",*'"

or

"SELECT * FROM paymaker WHERE  InStr(1, ',' & userid & ',', '," & varjobs & ",') > 0"


(Since you're in an ASP - you're likely using DAO - and so would replace * as wildcard with % if using the first one).
0
 

Author Comment

by:budgilbert67
ID: 17150057
Thank you for your help LPurvis, I tried using both of these querys and did not have any success.   As you will notice I have changed userid to ID because my original query was wrong.   This is the error log from my server, let me know what I my have done wrong.
|20|80040e14|[Microsoft][ODBC_Microsoft_Access_Driver]_Syntax_error_in_string_in_query_expression_''_'_&_[ID]_&_'_'_Like_'%__+_Replace(Recordset1__varjobs_'.,
 This is the way I wrote the query:
SELECT *
FROM paymaker
WHERE ',' & [ID] & ',' Like '%," & varjobs & ",%'
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17150266
Hmm... Those quotes ain't right.. But we need a subtle re-think perhaps.
You'll probably really want the eventual output string for your SQL to be

SELECT *
FROM paymaker
WHERE ',12,23,34,345,' Like '%,' & ID & ',%'

So the string you need to build will be more along the lines of

strSQL = "SELECT * FROM paymaker WHERE '," & varjobs & ",' Like '%,' & [ID] & ',%'"

Give that a whirl.
0
 

Author Comment

by:budgilbert67
ID: 17150282
jimpen, I tried your query and this is the error log from my server:
|20|80040e14|[Microsoft][ODBC_Microsoft_Access_Driver]_Wrong_number_of_arguments_used_with_function_in_query_expression_'instr(1__comma_jobs__392_393_391_394_395_396)_<>_0'.,
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17150320
ODBC?

How do you connect to the data?  I was assuming it would be through the OLEDB provider - but perhaps you're using an ADO connection - with an ODBC provider?
0
 

Author Comment

by:budgilbert67
ID: 17150359
I am using a system DSN through ODBC provider if that helps
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17150479
Yeah - figured as much.

I haven't done a lot of linking to jet data through ODBC (unsurprisingly) - however I don't think that should make a difference.
It's ADO's rules we're following - rather than the providers.
0
 

Author Comment

by:budgilbert67
ID: 17151135
So, what do I need to do to make this query work?  Lpurvis
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17151239
Was the suggestion of http:#17150266 not helpful?

How are you actually opening the recordset based upon it?
Are you sure there's no problem?
Can you open a simple one without parameters?
0
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 17153308

  Kinda surprised no one has mentioned this; one of the reasons that your having a problem with this is that you've broken one of the basic rules of normalization.  That is, you have a field whose value is not a single value.  The field comma_jobs should be broken out into a seperate table.

  If you can correct the database design, NOW is the time to do it before you go any further.  

  If you can't modify the design, then try:

"SELECT * FROM paymaker WHERE  InStr(1, [userid], " & chr$(34) & varjobs & chr$(34) & ")>0"

  assuming you are generating this SQL statment in code.

Jim D.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 17153376
Are you doing ADO queries or are they linked tables.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17153477
I'd read this as there being a table (paymaker) with a field userid/ID (possibly a PK or FK - who knows) but that this field has a single numeric value.
Yep?  (I think we're all agreed on that?)

And I'd supposed that the comma delimted list is criteria passing from a previous web page to another.  (Based on "Request.form" comment in question and subsequent ODBC link).

Now it did indeed sound at the beginning as if a field itself contained the delimted list (which would indeed be bad) and it may be that the criteria comes from such a field rather than somehow concatenated criteria controls on the posting form page.
(This should be looked at if so as Jim says).

But ultimately - the requirement has become to match on UserID's in the passed concatenated variable list.
Yes - the other suggestions of Instr are one of the ways (made such a suggestion myself too), but the commas have to be taken into consideration, which is where all the seeming complexity in my posts has stemmed.  I don't believe I'm over complicating it though.
But I'd have imagined that the suggested strings would work.
(Many of them in fact - to one extent or another).

Hence the question about how the data is being accessed.
If there might be a problem there.
If a simple SQL statement can be executed.
And so a look at a larger portion of the (likely ASP VBScript?) code might help.

Hence my verison or the Instr method would be

strSQL = "SELECT * FROM paymaker WHERE  InStr(1, '," & varjobs & ",', ',' & userid & ',') > 0"
and still stand by
strSQL = "SELECT * FROM paymaker WHERE '," & varjobs & ",' Like '%,' & [ID] & ',%'"

But we'll see what budgilbert67 comes back with.
0
 

Author Comment

by:budgilbert67
ID: 17157697
Lpurvis, You are correct in stating that the comma seperated values are passed in a form variable called comma_jobs.  These values are the ID's of a table called paymaker.   They are passed along looking like this:  223,224,224,226, etc.   I need to gather all of the records from paymaker that these ID's belong to.   I am sure that you have already figured as much.   I have tested these queries, but have no success.   I am using ADO's and not linked tables.   I hope that this helps.   Thank You
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17157770
Can we see your code then?
0
 
LVL 54

Expert Comment

by:nico5038
ID: 17159739
Having a string like 223,224,224,226, etc. will enable the use of the IN() predicate in the WHERE clause like:

strSQL = "select * from paymaker WHERE  userid IN (" & varjobs & ");"

This does assume your userid is numeric !

When you have multiple records with this string then a recordset processing loop will be needed to fill the varjobs for each row.
Another option is to generate a table with the values from varjobs in separate rows with the ID of the record they come from. Such a relation table is what JDettman is pointing to with the "normalization".

Nic;o)
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17159826
Indeed Nico has given yet another option - (you'd have to be careful that the passed string was nicely formatted i.e. no leading comma or empty values) but I still feel that the execution problem being encountered may lie elsewhere than the SQL syntax.

(Unless the earlier examples were "interpreted" incorrectly before implementation).

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17159959
Leigh,

>>Indeed Nico has given yet another option<<
Scroll up and take a look at the second comment in this thread.  I suspect you may have overlooked it.

Anthony
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17159977
lol I remember reading your posts Anthony - but that must feel so long ago to me now that I'd completely forgotten what you actually posted :-)
Sorry about that.  (I'd normally be pointing out such things and making sure there aren't repeat suggestions).

So I now reckon I should have said:
Nico has just re-iterated Anthony's earlier suggestion - but I feel that blah blah.. code execution.. blah blah :-)

I will be interested to see the ASP code though.
(Assuming that is what it is).
0
 
LVL 54

Expert Comment

by:nico5038
ID: 17159981
Sorry Anthony, missed that too :-(

Nic;o)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17160299
No problem.
0
 

Author Comment

by:budgilbert67
ID: 17160815
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_accessdsn_STRING
Recordset1.Source = "SELECT *  FROM paymaker  WHERE '," & " + Replace(Recordset1__varjobs, "'", "''") + " & ",' Like '%," & [ID] & ",%'"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17161041
I guess Recordset1__varjobs is the variable - obtained from some other ADO recordset that you have open at a predetermined row?
And you want to use that value as the comma separated list...?

Your string isn't going to give the desired results.
You'd want something more like

"SELECT *  FROM paymaker  WHERE ',' & " & Replace(Recordset1__varjobs, "'", "''") & " & ',' Like '%,' & [ID] & ',%'"
or just
"SELECT *  FROM paymaker  WHERE ',' & " & Recordset1__varjobs & " & ',' Like '%,' & [ID] & ',%'"

Of course, that said - the 'In' example (as first mentioned by Anthony some time ago - obviously ;-) would probably be easier on the eye.
But as I mentioned - your comma separated list will have to have appropriate formatting...

"SELECT *  FROM paymaker  WHERE [ID] In (" & Replace(Recordset1__varjobs, "'", "''") & ")"
or possibly even just
"SELECT *  FROM paymaker  WHERE [ID] In (" & Recordset1__varjobs & ")"

as I'm uncertain as to why there would need to be a quote (delimitation) replace on data that should only be a list of numeric values.
(And ID is numeric too yes?)

Replace("1,2,3,4,5", "'", "''") = 1,2,3,4,5
You know what I mean - seems totally pointless.

If I were to recomend any replace at all - it would be to remove the space characters - particularly for the 'Like' method.

So that leaves you with
"SELECT *  FROM paymaker  WHERE ',' & " & Replace(Recordset1__varjobs, " ", "") & " & ',' Like '%,' & [ID] & ',%'"
or
"SELECT *  FROM paymaker  WHERE [ID] In (" & Recordset1__varjobs & ")"

Good luck!
0
 

Author Comment

by:budgilbert67
ID: 17161568
Thanks guys, I have tried all of these querys and everyone of them give me an error that varjobs is an invalid variable name and that it does not appear in the sql.   I am starting to think that my best option here is to do like Nico has suggested and use a relationship table and do away with the comma Idea.  What are your feelings here?
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17161714
varjobs alone wasn't even mentioned in the above post... :-S
What actually was the Recordset1__varjobs that was being referred to?
If you've supplied the correct information with variables passed as you expect them to be - then I don't see what's left to go wrong that wouldn't go wrong under any scenario...
(<Confusion>)

Anyway - yes ideally you should have related information stored in some form of link table as suggested earlier (it was Jim Dettman who originally mentioned it).
You'd then just have to appropriately change your SQL statement to do all the work in one (possibly still passing a simple parameter rather than a range to it).
0
 
LVL 54

Expert Comment

by:nico5038
ID: 17162599
In Access you can use a function to split the comma separated string and transform it into a table like:

function fncSplit()

dim rsI as DAO.recordset
dim rsO as DAO.recordset
dim intI as integer
dim arr

set rsI = currentdb.openrecordset("paymaker")
set rsO = currentdb.openrecordset("newtable")

while not rsI.eof
   arr = split(rs!varjobs)
   for intI = 1 to ubound(arr)
        rsO.Addnew
        rsO!ID = rsI!ID
        rsO!varjob = arr(intI)
        rsO.Addnew
   next
   rsI.movenext
wend

set rsI = nothing
set rsO = nothing

end function

Just copy/paste this code in the modules section and press F5 to run the code when the cursor is positioned in the code.
Also make sure you created the new table with ID and varjob as fields.

Nic;o)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17197574
Leigh,

My condolencies on the outcome, but I had been down this road before.  See here:
http://www.experts-exchange.com/Databases/Q_21913140.html

Hence my lack of enthusiasm in contributing in this thread.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17199936
Ah ha.  That's totally fair enough - and I now share your lack of enthusiasm for similar future threads :-)
0

Featured Post

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question