Generate a value from other values ...

[Chunk]

I want to have a coloumn called "level" which is generated depending on what "height" and "width" and "age" have been set to.

I have a table:

stats(name,height,age,width,level)

i want level to be worked out automatically, such that:

if height > 10 and wieght > 10 and age 10
then level = 1
else

if height > 5 and wieght > 5 and age 5
then level = 2

else level = 3


How can I go about setting this up?

Thanks.

 

[Pat Hartman]

1. create a function that calculates Level and use it in any query where you want to see level:
Select name,height,age,width, CalcLevel(height,age,width) as Level
From Stats;
2. Name is a poor choice for a column name since it is the name of a common property. This name WILL cause problems if you need to use VBA for anything. Do not use function or property names or any other reserved words for that matter as column names. Also avoid embedded spaces and special characters.

 

[Chunk]

Ive written the function, but I get "Undefined function calcLevel", when I try to use it an a query.

 

[Chunk]

Following Pats guide, ive now got the form to display the calculated level. How can I now save the value it calculates into a record of my choice, when the user saves the rest of the record?

 

[Pat Hartman]

There isn't any reason to store the field especially when you can calculate it from other fields in the same record. Just use a query as the recordSource for your forms and reports.