By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,919 Members | 1,625 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,919 IT Pros & Developers. It's quick & easy.

Convert

P: n/a
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

May 9 '07 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Create a 2 column table with the number in one column and the text in the
2nd, Then use an update query to convert the number to text.

But, once you have the new table why convert? Use the table to provide
lookup on the forms, which will greatly improve you data consistency. Your
users would never need to see the number.

"Elainie" <El**************@bsc.wales.nhs.ukwrote in message
news:11**********************@h2g2000hsg.googlegro ups.com...
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

May 9 '07 #2

P: n/a
On May 9, 2:38 pm, "paii, Ron" <p...@packairinc.comwrote:
Create a 2 column table with the number in one column and the text in the
2nd, Then use an update query to convert the number to text.

But, once you have the new table why convert? Use the table to provide
lookup on the forms, which will greatly improve you data consistency. Your
users would never need to see the number.

"Elainie" <Elaine.Macint...@bsc.wales.nhs.ukwrote in message

news:11**********************@h2g2000hsg.googlegro ups.com...
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- Hide quoted text -

- Show quoted text -
I have done that now and it works well, how do I edit it if I want to
add some more data?

Elaine

May 9 '07 #3

P: n/a

"Elainie" <El**************@bsc.wales.nhs.ukwrote in message
news:11**********************@e51g2000hsg.googlegr oups.com...
On May 9, 2:38 pm, "paii, Ron" <p...@packairinc.comwrote:
Create a 2 column table with the number in one column and the text in
the
2nd, Then use an update query to convert the number to text.

But, once you have the new table why convert? Use the table to provide
lookup on the forms, which will greatly improve you data consistency.
Your
users would never need to see the number.

"Elainie" <Elaine.Macint...@bsc.wales.nhs.ukwrote in message

news:11**********************@h2g2000hsg.googlegro ups.com...
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- Hide quoted text -
- Show quoted text -

I have done that now and it works well, how do I edit it if I want to
add some more data?

Elaine
1) Provide a form to edit the lookup table.
2) If you want your users to add choices, look at the "Limit to list"
property and the "Not in List" event of the combo box.
May 9 '07 #4

P: n/a
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.RecordSource 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.ColumnCount 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.Code; FirstOption.Translation; SecondOption.Code;
SecondOption.Translation; 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;Due;3;Received;4;Problems;5;Cleared

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.nhs.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

May 9 '07 #5

P: n/a

"King Ron" <Ki***************@covad.netwrote in message
news:11**********************@e65g2000hsc.googlegr oups.com...
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.RecordSource 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.ColumnCount 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.Code; FirstOption.Translation; SecondOption.Code;
SecondOption.Translation; 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;Due;3;Received;4;Problems;5;Cleared

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.nhs.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

There are many posts in this group explaining the pitfalls of using the
lookup feature in tables. Best practice appears to be, keep the lookup in
the forms using combo box or list box.
May 9 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.