# Thread: Multiple criteria to determine category (2000-SR1)

1. ## Multiple criteria to determine category (2000-SR1)

To place orders for gowns, the data is collected on a paper form with height given in feet and inches, and weight in pounds. There are 27 different gown size choices; the first six are shown in this table:
4'10"-5'0" (1) <160 (2) 160-219 (3) >220
5'1"-5'3" (4) <180 (5) 180-239 (6) >240

So, if the person is 5

2. ## Re: Multiple criteria to determine category (2000-

Have a look at the attached demo.

It uses a table to hold the various sizes , then retrieves the right size using a lookup from a query based on the table. The values in the table are all 1 more than the thresholds.

This avoids having all the complicated iff statements.

My demo just uses one text box for height (in inches) , but it makes sense to have one for feet and one for inches and calculate the height in inches from them.

I am also triggering the lookup with a command button, but you could use an after update event. You just need to think through how to trigger it so it does not try to do a lookup until you have all the data you need.

3. ## Re: Multiple criteria to determine category (2000-SR1)

You could create a function whose input is height and weight, and which returns the gown size. You wouldn't even have to store the gown size, you could easily calculate it whenever you needed it. For example, on your dataentry form, you'd have a textbox for GownSize, whose controlsource would be something like: =GetGownSize(PersonHeight,PersonWeight).

4. ## Re: Multiple criteria to determine category (2000-

I was thinking of storing the gown size in a query that will be used for several different reports, so that would minimize the times I would need to specify that? I have the formula in a query to convert the "Feet and Inches" to inches...
In your example, "GetGownSize" is the name of the function? Pardon my ignorance...where or how does the function look up the gown size? (I have created a table with the gown size criteria listed; sample records below:
Size SizeCode Height Weight
Regular 1 4'10"-5'0" <160
Weight*1 2 4'10"-5'0" 160-219
Weight*2 3 4'10"-5'0" >220
Regular 4 5'1"-5'3" <180
Weight*1 5 5'1"-5'3" 180-239
Weight*2 6 5'1"-5'3" >240

Thanks for whatever help is offered!

5. ## Re: Multiple criteria to determine category (2000-

If you were to follow Mark's suggestion, it is entirely up to you how the function GetGownSize would do its job.
You would have to write it from scratch.
You could use a lookup, or write a whole lot of if statements, or whatever else you can think of that works.

6. ## Re: Multiple criteria to determine category (2000-

I'm still not visualizing how this would look and work. I wasn't sure if Mark's suggestion was a modification of yours, or what. As you can tell, I am pretty green with a lot of this.
To write a function myself would require 27 different statements, I think. I'll see what I can figure out from the lookup angle.
Thanks

7. ## Re: Multiple criteria to determine category (2000-

There is overlap and difference between my suggestion and Mark's.

One of the general principles is that you don't store a piece if information if you can calculate it from other stored data. So Mark is saying that gown size can be calculated from heght and weight, so should not be stored in a table. To make the process of calculating it easy in practice, put the calculation process into a function.

One of the reasons for not storing anything that can be calculated is that you have to be sure that you update the stored value whenever any of the inputs change.
Sometimes the calculation is lengthy, and the inputs rarely change, so it makes sense to store the result for performance reasons.

In this case you could do it either way.

8. ## Re: Multiple criteria to determine category (2000-

Got it working now...thanks for the help.
Warren

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•