How to change a datatype to a number

[lg5050]

I have a problem with changing a datatype into a number. The thing is that all the fields are in text and i want to change some of them to a number datatype. If i try to change the field to a number i get a message that come up as This:
The setting for the field size property of on or more fields has been changed to a shorter size. If data lost, validation rules may be violated as a result. I try to export it into an excel file and change all the field that i need to be a number and convert them into a number and it work by when i import them back into access they don't change. I don't understand what to do. Any help please.

 

[CJ_London]

The setting for the field size property of on or more fields has been changed to a shorter size.

This just means that your text field is perhaps 50 chars long, whereas the maximum number size is 16 characters (assuming you set the number to double - 8 if it is long). Any fields with a none numeric character will convert to null.

Suggest take a copy of the table then covert that and see if it is OK - if it its, job done. If you have a large amount of data you may get the message that there are insufficient resources to complete the exercise.

If this is the case, open the table in design view and add another field as a number of the type you want.

Then create an update field to update the new field with the value from the text field. Once run, delete the text field and rename the new field.

 

[lg5050]

I tried to copy and paste it to another new table and it did the same thing.
For the other question it has a lot of data in it like 40790 records. i was thinking about create a new field programmatically, transfer all the data from the old field into it but i don't know how to get to that point. Like where would i put the vb code at.

 

[lg5050]

i tried it and it did the same thing. Any other issue i could do.

 

[gulabgang]

Relationship links between them look OK and all of them are marked to enforce referential integrity, cascade update
related fields and cascade delete related record

adil

 

[essaytee]

i tried it and it did the same thing. Any other issue i could do.

What did you try? A number of suggestions were offered and yet from your reply you refer to only one (I tried it).

Did you try the create a new field, use an update query to populate that field with the data?

 

[essaytee]

Relationship links between them look OK and all of them are marked to enforce referential integrity, cascade update
related fields and cascade delete related record

adil

I'm confused, where did you get this information? The OP never revealed it.