Code for entering criteria within a query?

M

Matt

I have a query that contains 10 yes/no data type fields, and some other
calculated fields from a customer table. The query is displayed in a subform
within a form.
There is a combo box that I want the user to select which will then locate
the corresponding yes/no field in the query and enter TRUE in the criteria.
The code will then requery the subform and the user can view the correct
yes/no field conrtaining True values only. Is there code that will perform
say a Dlookup in the query and set the criteria to true or false? I tried
using a huge IIf statement, however I ran out of room in my expression
builder.

Thanks,
Matt
 
A

Allen Browne

Matt, the problem here is with the data design.

Instead of using many yes/no fields for selecting the relevant values, you
need to create a related table and enter a *record* (not a field) for each
value that applies.

You currently have a field that has fields like this:
ClientName
Roses Y/n
Violets Y/n
Snapdragons Y/n
...

This needs to become 3 tables:
1. Client table:
ClientID primary key
ClientName Text
...

2. Flower table:
FlowerID primary key
FlowerName Text

3. ClientFlower table:
ClientID foreign key to Client.ClientID
FlowerID foreign key to Flower.FlowerID

The interface is a main form bound to the Client table, with a subform bound
to the ClientFlower table. The subform contains a combo where you can select
a flower. You add another row to the subform for every flower that applies
to the client in the main form.

That structure allows you to do any querying you want to.

Technically, you have a many-to-many relation between clients and flowers
(one client can like many flowers, and one flower can be apply to many
clients), and the 3rd table (called a junction table) is used to break that
into a pair of one-to-many relations.
 
Top