help needed entering a record based upon value of another field

[Guest]

I have a Table Field called Gender which records either Male or Female
I need another Gender Field that records a persons Gender as 1 or 2 or 9
1=Male
2=Female
9= Not Known or inadquatley described

The 1,2 or 9 is used in comma deliminated report file, however the end user
needs "plain english"
Is there a way I can update Gender Field 2 based upon Gender Field 1
IF(Gender=Male,1 ELSE IF Gender=Female,2,9) type of thing?

 

[Douglas J Steele]

You definitely should NOT have both fields in the same table.

What you should have is strictly the numeric value in your main table, plus
a second table that has 3 rows: one for each of the 3 possible values, with
a description of what each value means.

You can create a query that joins the two tables together when you want the
text to appear, and use the query rather than the table. In your update
form, you can have a combo box that displays the text to the user, but
stores the numeric value.

 

[Guest]

Hi Landywednak - If I am understanding you correctly (not quite sure what you
mean by a comma delimited report) you do not need a second field as you can
use the IF statement in the report, and on forms etc.
IIF([gender]="male",1,IIF([gender]="female",2,9))
If you really do need a second field to hold the 1,2,9 an update Query can
create your new fields based on data in the current one. Give a shout if you
need more info.

Yours - Dika

 

[Joseph Meehan]

I have a Table Field called Gender which records either Male or Female
I need another Gender Field that records a persons Gender as 1 or 2
or 9 1=Male
2=Female
9= Not Known or inadquatley described

The 1,2 or 9 is used in comma deliminated report file, however the
end user needs "plain english"
Is there a way I can update Gender Field 2 based upon Gender Field 1
IF(Gender=Male,1 ELSE IF Gender=Female,2,9) type of thing?

Using an additional field would be a bad idea. These things tend to
create errors.

Use a look up. I might suggest using the numbers in the primary table
and then when you want to display the text version, use a linked look-up
table.

 

[Joseph Meehan]

Using an additional field would be a bad idea. These things tend
to create errors.

Use a look up. I might suggest using the numbers in the primary
table and then when you want to display the text version, use a
linked look-up table.

Make that RELATED look-up table, not linked.