Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 649
  • Last Modified:

Search from a comma seperated field

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
budgilbert67
Asked:
budgilbert67
  • 12
  • 9
  • 6
  • +3
1 Solution
 
Anthony PerkinsCommented:
>>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
 
Anthony PerkinsCommented:
You can also use something like this:

Dim SQL
SQL = "SELECT * FROM paymaker WHERE userid In (" & varjobs & ")"
0
 
budgilbert67Author Commented:
How do I movie this question to MS Access?
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
budgilbert67Author Commented:
How do I move this question to MS access?  Can't spell today
0
 
Anthony PerkinsCommented:
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
 
Jim P.Commented:
SELECT *
FROM paymaker
WHERE  instr(1, comma_jobs, varjobs) <> 0
0
 
Leigh PurvisDatabase DeveloperCommented:
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
 
budgilbert67Author Commented:
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
 
Leigh PurvisDatabase DeveloperCommented:
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
 
budgilbert67Author Commented:
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
 
Leigh PurvisDatabase DeveloperCommented:
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
 
budgilbert67Author Commented:
I am using a system DSN through ODBC provider if that helps
0
 
Leigh PurvisDatabase DeveloperCommented:
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
 
budgilbert67Author Commented:
So, what do I need to do to make this query work?  Lpurvis
0
 
Leigh PurvisDatabase DeveloperCommented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

  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
 
Jim P.Commented:
Are you doing ADO queries or are they linked tables.
0
 
Leigh PurvisDatabase DeveloperCommented:
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
 
budgilbert67Author Commented:
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
 
Leigh PurvisDatabase DeveloperCommented:
Can we see your code then?
0
 
nico5038Commented:
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
 
Leigh PurvisDatabase DeveloperCommented:
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
 
Anthony PerkinsCommented:
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
 
Leigh PurvisDatabase DeveloperCommented:
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
 
nico5038Commented:
Sorry Anthony, missed that too :-(

Nic;o)
0
 
Anthony PerkinsCommented:
No problem.
0
 
budgilbert67Author Commented:
<%
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
 
Leigh PurvisDatabase DeveloperCommented:
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
 
budgilbert67Author Commented:
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
 
Leigh PurvisDatabase DeveloperCommented:
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
 
nico5038Commented:
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
 
Anthony PerkinsCommented:
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
 
Leigh PurvisDatabase DeveloperCommented:
Ah ha.  That's totally fair enough - and I now share your lack of enthusiasm for similar future threads :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 12
  • 9
  • 6
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now