querying single text entry from multiple text field

  • Thread starter Thread starter Grub via AccessMonster.com
  • Start date Start date
G

Grub via AccessMonster.com

Hello,

I am working on an Equipment database in Access 2002, it is working out well.
Except for one thing.
On the database switchboard there is a button for "Search by Cartridge". It
pulls up a form from a query of a Equipment Table.

The query is set up as [Enter Cartridge] criteria under the Cartridge field,
and all the other fields in the query that display for the form are pulling
from the "Equipment Log" Table also.

Once the cartridge is entered it should pull up all models of equipment from
the table that have that particular cartridge entered. But it will only pull
up single entry cartridges thus far. Some models of equipment have 6
cartridges! listed in a row.

My problem is the "Cartridge" field in the "Equipment Log" table has
multiple cartridges all listed in a row, some separated by a comma, some not.

I wanted to know if there was a criteria I could put into my query so that
when someone uses the query and enters the [Enter Cartridge] whether it could
specify that particular text out of a line of text in that field.

Otherwise I thought of making 6 rows of cartridge entries in my Equipment
Table, but that seems excessive, let me know if you can help.

Thank you.

Grub.
 
Perhaps using criteria of
LIKE "*" & [Enter Cartridge] & "*"
will solve your problem. This will basically search for a match anywhere in
the string.

The source of this problem is the fact that you have more than one value in
the field (on at least some records). What you really need is to redesign
your structure to have a table hold all the cartridges for each record in
the main table

NewTable: One record for each combination of existing record and the
cartridge types being stored.
CartridgeTable
Cartridge
PrimaryKeyFieldOfOldTable
 
Thank you John!, I went from having no idea, to having it solved in 1hr and a
half.

I played around with the criteria you suggested until I got the results I
wanted.

I placed > Like [Enter Cartridge] & "*" < in the cartridge criteria, and it
works a charm.

It picks up the text out of my multiple entry fields everytime.

Regards.
Grub.


John said:
Perhaps using criteria of
LIKE "*" & [Enter Cartridge] & "*"
will solve your problem. This will basically search for a match anywhere in
the string.

The source of this problem is the fact that you have more than one value in
the field (on at least some records). What you really need is to redesign
your structure to have a table hold all the cartridges for each record in
the main table

NewTable: One record for each combination of existing record and the
cartridge types being stored.
CartridgeTable
Cartridge
PrimaryKeyFieldOfOldTable
Hello,
Click to expand...
[quoted text clipped - 33 lines]
Grub.
Click to expand...
Click to expand...
 
Back
Top