Dear Steve:
Originally, your code was:
SELECT yourFields from yourTable WHERE
INSTR(',' + CSTR(Sample_ID) + ',', ',' + [user string entry] + ',') > 0
Please try:
SELECT yourFields
from yourTable
WHERE INSTR("," & [user string entry] & ",", "," & CSTR(Sample_ID) & ",")
When it prompts, please enter:
3,5
Does this return the two rows desired (assuming they both exist!)
I have used double quotes and the & concatenation operator (unless you're
using SQL Server) to perform the concatenation. These should be your
standard practices with Jet. The single quotes and "+" concatenation
operators had me thinking this is for SQL Server, where that would be
typical. Or, perhaps, you're more accustomed to SQL Server and write things
for that automatically (just as I automatically read your code, in part).
However, the use of a prompt must mean this is Jet, so we had better stick
with one or the other.
Tom Ellison
Steve D said:
Still no results. I am entering ,3,5,3
I am entering ,5,3, - nothing
The only way anything comes back is if I enter only ONE sample_id BUT
this
query will be the basis for a report that could call up to 10 sample_id's.
Why is this one so elusive? Any other suggestions???
--
Thanks,
Steve D
Tom Ellison said:
Dear Steve:
Now I believe I can see the problem. The Instr() function has you
entering
the string:
",3,5,"
As its FIRST parameter, not the second, and the "string being sought"
",3,"
as the second parameter. It appears you have them turned around. Does
that
help?
This should have been evident to me from your first post, but I have been
known to turn them around myself. You probably should have asked someone
else! : )
Tom Ellison
Thanks for hanging in there with me Tom. The sample_id uses the auto
number
data type. I have 3 test records in the db which have id numbers 3, 4
and
5.
When i run the query, the parameter query dialog box opens. If i enter
ONLY
one id (either 3, 4 or 5) the query result comes back with all the
fields
in
the recordset that are needed. If i enter, i.e. 3,5 the query result
comes
back having all the field names listed but the fields are blank - no
data
showing at all.
Hope this helps you and thanks again for working with me.
--
Thanks,
Steve D
:
Dear Steve:
Before going as far as to email me your database, let's get into some
specifics here.
What is in the column Sample_ID for which you expect to search? What
did
you type into the [user string entry] to search it? And what does it
mean,
exactly, "it will not work"
Your computer crashes? It just doesn't find what you expect it to
find?
It
gives some error message?
There are causes for this problem that I might not find using your
database
if I did have it. It could be the way you're using it. It could be
"references" specifically to the INSTR() function. It might work on
my
computer but not on yours. We'll cover those before I give up and
recommend
you sending me the database. In my experience this is a more useful
approach for now.
We'll keep the option of sending the database tabled for now, please.
Besides, if I can get you to do all the work and I just sit back and
grin,
that's a lot more fun.
Tom Ellison
Thanks for your input. I have followed your instructions but the
query
does
not produce the desired result. I really don't get it. I wish I
could
e-mail you a .zip of my db for you to look at.
Any other thoughts, comments?
--
Thanks,
Steve D
:
I have this query started ...
SELECT yourFields from yourTable WHERE
INSTR(',' + CSTR(Sample_ID) + ',', ',' + [user string entry] + ',')
0
...
BUT for some reason it will not work when i enter more than one
sample_id.
what am i doing wrong / not doing ???
