Text Box Selection Criteria

[KP]

Hi, I hope you are doing well.

I have a text field in a table. Each data entry in the
table for this field starts with a prefix to identify the
data. For example, I'll call it Field1. The data looks
like this:

Blue - Rest of text
Red - Rest of text
Brown - Rest of text

On a form I want to be able to select entries from the
record that matches one of the prefixes. I can do this
through the query with Field1, which is not a problem
(Like "blue*"). However, I want to include on the form
other text boxes I can use to select the other entries
using the LIKE "red*" or some other expression.

Question = How do I create an expression in the query or
in the form to select the REDs, BROWNs, etc and show them
when they exist?

Thanks in advance.

 

[Allen Browne]

You could create a calculated field in your query that parses the first word
of the field, using Instr() to find the first space, and Left() to get the
word before that. If calculated query fields are new, see:
http://allenbrowne.com/casu-14.html

However, there is a much more elegant way to solve this problem. Create an
extra field, and put the identifying data into that field. You will find
this *hugely* more flexible and efficient. That's probably why one of the
basic rules of normalization is to ensure your data is atomic (i.e. only one
thing in each field).