extract one value from multi-value record

  • Thread starter Thread starter Molly
  • Start date Start date
M

Molly

I have a table with the following fields: RecordID (the
Primary Key field, autonumber format), Date (defaults to
today's date in the absence of user input, date/time
format), and TrapID. The TrapID field receives its data
from a multiple selection list box I set up in a form.
This list box allows multiple values to be selected and
then saved as ONE record in my table. These multiple
values are stored as a semicolon-separated list. If you
need the explicit details on the code I used to create
such a list box and table, please see Knowledge Base
Access Article #140483.

Unfortunately, I need to be able to sort on any individual
value stored in the TrapID field. Here are a couple sample
records from my table:

RecordID: Date: TrapID:
1 11/3/03 100; 104; 105
2 11/5/03 103; 109; 122
3 11/6/03 100; 107; 110

So in other words, I need to create a query which tells me
what dates correspond to the trap ID I input as critera (I
enter 100 as criteria, the query should return records 1
and 3; I enter 109, it returns record 2).

I know it's possible to do this sort of thing, I just
can't seem to find out how. I'm pretty much a beginner at
Access, so any help (in easy to understand terms!!) would
be greatly appreciated. I hope I've been clear about all
my definitions and explanations of how my tables are set
up, but please ask me if you're unsure exactly what I'm
trying to do.

Many thanks in advance.
 
To solve your problem, you might want to test using Instr
or Like.

Expr1: Instr([YourField], "YourSearchValueHere") > 0

If True (you'd say this in the Criteria line), then you'd
have a hit.

You can also use Like (but Like and Speed are not friends).

In the Criteria section of your query for your field you
want to search on, you could place asterisks around your
search value. Something like:

Like *YourValueHere*


Of course, the real solution is not to use your
construction, but rather use proper database constructions.

David Atkins, MCP
 
David,

Thank you very much for your solution. I tried it (using
the InStr method you offered) and it works exactly as I
needed it to. I was even able to write in a prompt so that
I can specify a different value every time I run the
query. Great!!!

As for the database design, I know it's not good. I just
couldn't figure out a way to get each value to save as an
individual record and still only have to click my "save"
button once on the form that contains the multiple
selection list box. If you have any insight into this
problem, I'm very happy to hear it. I know unique records
will be infinitely more workable in the long run.

Thanks again,
Molly
 
Molly said:
David,

Thank you very much for your solution. I tried it (using
the InStr method you offered) and it works exactly as I
needed it to. I was even able to write in a prompt so that
I can specify a different value every time I run the
query. Great!!!

As for the database design, I know it's not good. I just
couldn't figure out a way to get each value to save as an
individual record and still only have to click my "save"
button once on the form that contains the multiple
selection list box. If you have any insight into this
problem, I'm very happy to hear it. I know unique records
will be infinitely more workable in the long run.
Click to expand...

Why do you have a save button at all?
 
Well, I know that if I simply close the form, the values
are saved. Likewise, I can leave the record selector
button visible at the bottom of the form, and use
the "new" button to save the current record and go to the
next one. The only problem with either option is that this
database is being built for use by people who have minimal
computer skills, let alone any database knowledge. Also,
when the database is ready for these people to use, no
training regarding its use will be offered. Therefore, I'm
trying to be as obvious as possible in all controls and
navigational buttons so that the chances of someone making
a mistake are minimal.

If you know of a better way to accomplish this "user-
friendliness" and still retain functionality, please let
me know. Even though I'm in charge of designing this DB,
much like my aforementioned future users, I've never
gotten any training in Access or VisBasic or any other
database/coding program. I'm basically just blundering
through, trying everything I can think of until something
works. Not the most time-efficient, but right now it's the
best I can do.

Thanks for your input,
Molly
 
Back
Top