If statement in a table

  • Thread starter Thread starter blake7
  • Start date Start date
B

blake7

Hi,
I have a table with lots of columns, one of the columns is registering the
following criteria either - Warranty, Customer complaint, Health & Safety and
Internal issue.
I would like to add a further column that will return a fixed value eg 10
points if a Health and Safety issue is registered, 5 point for Customer
complaint etc, how can this be done, can it be done with an if statement in a
text box on the form which the table is linked to or can it be done directly
in the table?
Thanks. Tony
 
Hi,
I have a table with lots of columns, one of the columns is registering the
following criteria either - Warranty, Customer complaint, Health & Safety and
Internal issue.
I would like to add a further column that will return a fixed value eg 10
points if a Health and Safety issue is registered, 5 point for Customer
complaint etc, how can this be done, can it be done with an if statement in a
text box on the form which the table is linked to or can it be done directly
in the table?
Thanks. Tony
Click to expand...

Why bother?
The place to do a calculation such as this is in a query or on a form
or report.
Tables are not the place to do calculations.
All you need is the appropriate data... "Warranty", Customer
Complaint", etc.
Then in a query..
Points:IIf([FieldName] = "Warranty",5,IIf([FieldName] = Health &
Safety",10,IIf(etc...)))

In any event, do not store the point value.

Note: While I've given a simple solution, a better solution would be
to have a table with the various possible choices and their points.
Then just set the relationship to the table field and choice name
field and use the Point field directly in the table.
 
Back
Top