help needed entering a record based upon value of another field

[Landywednak]

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.

 

[Kernow Girl]

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