1. Add a new field to your existing table. Name it (say) FurnitureTypeID. It
can be a text field, and set its Field Size property to whatever is enough
(perhaps 24 or 40 characters.) Save the table.
2. To populate this new field, create a query into this table. Change it to
an Update query (Update on Query menu, in query design view.) Access adds an
Update row to the grid.
3. Drag the Furniture field into the grid.
In the Criteria row under this field enter:
0
4. Drag the FurnitureTypeID field into the grid. In the Criteria row under
this field, enter:
Is Null
In the Update row under this field, enter:
"chair"
5. Run the query, by clicking the exclamation icon on the toolbar.
6. Back to design view, change the Criteria under Furniture to:
1
and the Update row under FurnitureTypeID to:
"table"
Run the query again.
7. Repeat step 6 for the other values as well.
8. After verifying that all the entries are correct, you can delete the
original Furniture field.
To make the task much easier later, it would be a good idea to create a
table to hold all the valid values, and use that as the source for the
combo. To do that as well:
1. Create a new table named (say) FurnitureType, with one Text field named
FurnitureTypeID.
2. Create a query into your original table, selecting just the
FurnitureTypeID field. In the Criteria row under this field, enter:
Is Not Null
3. In the Properties box (View menu), set the query's Unique Values property
to Yes.
4. Change it to an Append query (Append on Query menu).
Tell Access you want to append to the FurnitureType table.
5. Run the query. The table now contains the valid choices.
6. Choose Relationships on the Tools menu, and add both tables to the
screen. Then drag FurnitureTypeID from the FurnitureType table onto the
matching FurnitureTypeID field in the other table. When you let go, Access
pops up the Create Relation dialog. Check the box for Relational Integrity,
and Ok the dialog.
You are now assured that only valid values can appear in the list, but it is
much easier to maintain than the value list.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"nyt" <na*****@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
I have a problem of number and text field.
I got the database file(mdb) that contains many combo boxes used and
its list values are created by "value list"
For eg field
Field name= 'furniture' , data type='Number' ,Display Control='Combo
Box', RowSource Type = 'Value List' and
Row Source = ' 0;"chair";1;"Table";2;"Bed" '
Therefore, in data sheet view of table, if we select (1 : Table ) ,
then the value 1 is stored in furniture field.
I want to change the data type of this field 'furniture' to 'Text' and
the data to be assoiated text 'Table' instead of '1' for all data
records entered.
Is there any way to transform all records? please!!!!!!!!!!! help me
i haeve thousands of records to change such fields from 'number' to
corresponding 'text'
Waiting ur reply as soon as possible ,
NYT
