Solved

Search from a comma seperated field

Posted on 2006-07-19
34
636 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
  • 12
  • 9
  • 6
  • +3
34 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>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
Comment Utility
You can also use something like this:

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

Author Comment

by:budgilbert67
Comment Utility
How do I movie this question to MS Access?
0
 

Author Comment

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

Expert Comment

by:Anthony Perkins
Comment Utility
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.
Comment Utility
SELECT *
FROM paymaker
WHERE  instr(1, comma_jobs, varjobs) <> 0
0
 
LVL 44

Expert Comment

by:Leigh Purvis
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I am using a system DSN through ODBC provider if that helps
0
 
LVL 44

Expert Comment

by:Leigh Purvis
Comment Utility
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
Comment Utility
So, what do I need to do to make this query work?  Lpurvis
0
 
LVL 44

Expert Comment

by:Leigh Purvis
Comment Utility
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 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
Comment Utility

  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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 38

Expert Comment

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

Expert Comment

by:Leigh Purvis
Comment Utility
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
Comment Utility
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
Comment Utility
Can we see your code then?
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Sorry Anthony, missed that too :-(

Nic;o)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
No problem.
0
 

Author Comment

by:budgilbert67
Comment Utility
<%
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Ah ha.  That's totally fair enough - and I now share your lack of enthusiasm for similar future threads :-)
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

771 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now