The best way to do this is to store the number but display the text
translation by changing the default 'DisplayControl ' property of the
table field from 'TextBox' to ComboBox.
You can approach this in a couple of ways. The most flexible way is to
create a separate table that stores the code and translation and then
set the ComboBox.Record Source to use the new lookup table as the
source. The advantage with the lookup table is that should you ever
need to add a new value to the lookup list, you can do it by adding a
row to the lookup table rather than altering the table structure. It
also makes it easier to display the translations when writing queries
and reports. I'll leave that method as an exercise for you to do once
you're comfortable with using combo box lookups.
Since this is a simple list of options and it looks as if the choices
are unlikely to change, you can do a simple value list ComboBox:
1) Open the table containing your 'Status' field:
2) Select the 'Status' field.
3) In the Properties section at the bottom, click the 'Lookup' tab
4) Change the .DisplayControl from 'TextBox' to 'ComboBox'. A number
of additional properties for the combo box will be displayed.
5) Set the .RowSourceType to 'ValueList'. If you were to use the
external lookup table method, you would set the .RowSourceType to
'Table/Query' and set the .RowSource to point to your lookup table.
Since for this exercise we only need those particular values in our
list, set the .RowSourceType to 'ValueList'
6) Set the .RowSource to the list of values you want to be stored and
displayed separated by semicolons. The .ComboBox.Colum nCount property
controls how the list will be handled. In this example, we want to
store the code but display the translation. You will enter each row in
order: FirstOption.Cod e; FirstOption.Tra nslation; SecondOption.Co de;
SecondOption.Tr anslation; etc. If you wanted to display more columns,
just add those values in order and set the .ColumnCount property
appropriately.
Using your example, set the .RowSource to:
..RowSource = 1;Inactive;2;Du e;3;Received;4; Problems;5;Clea red
7) Set the .BoundColumn to 1. The bound column is the column to be
'bound' or stored in the base table. You can use any column as the
bound column if all columns are to be displayed in the .ComboBox
dropdown. In this example, we want to hide the 'Code' value, so
the .BoundColumn needs to be the first column in the list.
8) Set .ColumnCount = 2. We have 2 columns in our list.
9) Set .ColumnWidths = 0";1.5" . The display width of the Code
value is 0 so it will be hidden from display. The user will only see
the Translated value.
10) Set .ListWidth = 1.5" This controls how wide the entire dropdown
will be and it's annoying to have the displayed values cut off by the
default width.
11) Save your table.
Since you already have numeric values in the field, on opening the
table you will now see the Code Translation displayed. However, you
need to be aware that you are actually still storing the numeric value
when you pick from the dropdown list. This is important because if you
search that field for 'Inactive' you will get no hits - the stored
value is actually 1.
Good luck!
Ron, King of Chi
On May 9, 8:28 am, Elainie <Elaine.Macint. ..@bsc.wales.nh s.ukwrote:
I would like to convert data numbers to text.... i.e.
1 = Inactive
2 = Due
3 = Received
4 = Problems
5 = Cleared
Their is numbers in the fields at the moment but they need to be
converted to the words.... I have tried everything that I can think of
at the moment.... if anyone can help.
Elaine