How to convert text fields to numeric fields without losing data in text fields.

[Frank]

I have a table in Access. It has a text field filled with numeric values.
How do I convert the text field to numeric without losing all the text data
in it that is numeric?

I appreciate any assistance.

 

[Klatuu]

Add a new numeric field to your table.
Run an update query that will update the new numeric field with the value of
the text field.
Delete the old text field.
Change the name of the new text field to what the old text field was.

 

[Frank]

Dave:

Thanks for tip. What do I put in the update to part of the query to update
all of the unique records?

 

[Klatuu]

Sorry, Frank, I don't understand your question. I don't see why you would
need to filter the query, don't you want to update all records?

 

[Frank]

Dave:

Thanks again.

I have various numbers in a text field - ie. 122 344, 234 666, etc. -
called [numbers]. I now have a blank numeric field called [test] in the
table.

I just want to convert all the numbers in the text field to numbers that
would populate the numeric field. Every time I run the update query, it
wipes out all the numbers in the text field. I must be doing something
wrong.

In update query, I chose the correct table, then in first row, I chose,
[numbers], the text field. In update to box I chose [test], the numeric
field. I run the query and it wipes out all date in the [numbers] field.

How do I resolve this?

Thanks much for your patience

 

[Frank]

Dave:

Thanks again.

I have various numbers in a text field - ie. 122 344, 234 666, etc. -
called [numbers]. I now have a blank numeric field called [test] in the
table.

I just want to convert all the numbers in the text field to numbers that
would populate the numeric field. Every time I run the update query, it
wipes out all the numbers in the text field. I must be doing something
wrong.

In update query, I chose the correct table, then in first row, I chose,
[numbers], the text field. In update to box I chose [test], the numeric
field. I run the query and it wipes out all date in the [numbers] field.

How do I resolve this?

Thanks much for your patience

 

[Klatuu]

Post the SQL of your udate query, and I will have a look at it.
--
Dave Hargis, Microsoft Access MVP

Dave:

Thanks again.

I have various numbers in a text field - ie. 122 344, 234 666, etc. -
called [numbers]. I now have a blank numeric field called [test] in the
table.

I just want to convert all the numbers in the text field to numbers that
would populate the numeric field. Every time I run the update query, it
wipes out all the numbers in the text field. I must be doing something
wrong.

In update query, I chose the correct table, then in first row, I chose,
[numbers], the text field. In update to box I chose [test], the numeric
field. I run the query and it wipes out all date in the [numbers] field.

How do I resolve this?

Thanks much for your patience

I have a table in Access. It has a text field filled with numeric values.
How do I convert the text field to numeric without losing all the text data
in it that is numeric?

I appreciate any assistance.

 

[Frank]

Here it is Dave.

UPDATE test SET test.numbers = [test];

Post the SQL of your udate query, and I will have a look at it.
--
Dave Hargis, Microsoft Access MVP

Dave:

Thanks again.

I have various numbers in a text field - ie. 122 344, 234 666, etc. -
called [numbers]. I now have a blank numeric field called [test] in the
table.

I just want to convert all the numbers in the text field to numbers that
would populate the numeric field. Every time I run the update query, it
wipes out all the numbers in the text field. I must be doing something
wrong.

In update query, I chose the correct table, then in first row, I chose,
[numbers], the text field. In update to box I chose [test], the numeric
field. I run the query and it wipes out all date in the [numbers] field.

How do I resolve this?

Thanks much for your patience

I have a table in Access. It has a text field filled with numeric
values.
How do I convert the text field to numeric without losing all the text
data
in it that is numeric?

I appreciate any assistance.

 

[John W. Vinson]

I have a table in Access. It has a text field filled with numeric values.
How do I convert the text field to numeric without losing all the text data
in it that is numeric?

I appreciate any assistance.

I think you need to post an example or two of the data, indicating what you
don't want to lose.

The Val() function will convert a text string which can be interpreted as a
number to a number, but I'd really be inclined to add a new numeric field to
the table and run an Update query updating it to Val([textfield]) rather than
changing the fieldtype.

You'll need to decide on the datatype of the number field first: do these
numbers have decimal places? If so you can't use the default Long Integer
number type. Do they exceed two billion? Likewise. Do you need fifteen or more
digits of precision? Good luck.....

John W. Vinson [MVP]

 

[Klatuu]

You have it backwards. If you want the numeric field test updated with the
value in the numbers:

UPDATE test SET test.test = [numbers];

The way you had it is why the numbers field was blanking out. Test has no
value in the field, and you were putting the Null value from the test field
into the numbers field.
--
Dave Hargis, Microsoft Access MVP

Here it is Dave.

UPDATE test SET test.numbers = [test];

Post the SQL of your udate query, and I will have a look at it.
--
Dave Hargis, Microsoft Access MVP

Dave:

Thanks again.

I have various numbers in a text field - ie. 122 344, 234 666, etc. -
called [numbers]. I now have a blank numeric field called [test] in the
table.

I just want to convert all the numbers in the text field to numbers that
would populate the numeric field. Every time I run the update query, it
wipes out all the numbers in the text field. I must be doing something
wrong.

In update query, I chose the correct table, then in first row, I chose,
[numbers], the text field. In update to box I chose [test], the numeric
field. I run the query and it wipes out all date in the [numbers] field.

How do I resolve this?

Thanks much for your patience
I have a table in Access. It has a text field filled with numeric
values.
How do I convert the text field to numeric without losing all the text
data
in it that is numeric?

I appreciate any assistance.

 

[Frank]

Hi John:

Thanks for assistance.

See below sample of data

test Number test
985 891 4101

985 845 4028

985 651 4528

985 542 5257

985 542 0064

985 345 1004

936 275 9916

936 275 9893

601 234 3892

507 695 2367

504 888 0912

504 887 6630



All numbers will be in above format. Note: Number col. is a text field and
Test col is a numeric - Long Integer Field. I don't want to lose the data in
the number field. When completed, both field should have the same data in
them.

Not sure how to run the update query to accomplish this.

Any assistance is appreciated.

I have a table in Access. It has a text field filled with numeric values.
How do I convert the text field to numeric without losing all the text
data
in it that is numeric?

I appreciate any assistance.

I think you need to post an example or two of the data, indicating what
you
don't want to lose.

The Val() function will convert a text string which can be interpreted as
a
number to a number, but I'd really be inclined to add a new numeric field
to
the table and run an Update query updating it to Val([textfield]) rather
than
changing the fieldtype.

You'll need to decide on the datatype of the number field first: do these
numbers have decimal places? If so you can't use the default Long Integer
number type. Do they exceed two billion? Likewise. Do you need fifteen or
more
digits of precision? Good luck.....

John W. Vinson [MVP]

 

[Frank]

this is the sql statement

UPDATE test SET test.test = [numbers];

I get error saying it can't update due to conversion error.

I tired this with following error message

You have it backwards. If you want the numeric field test updated with
the
value in the numbers:

UPDATE test SET test.test = [numbers];

The way you had it is why the numbers field was blanking out. Test has
no
value in the field, and you were putting the Null value from the test
field
into the numbers field.
--
Dave Hargis, Microsoft Access MVP

Here it is Dave.

UPDATE test SET test.numbers = [test];

Post the SQL of your udate query, and I will have a look at it.
--
Dave Hargis, Microsoft Access MVP


:

Dave:

Thanks again.

I have various numbers in a text field - ie. 122 344, 234 666,
etc. -
called [numbers]. I now have a blank numeric field called [test] in
the
table.

I just want to convert all the numbers in the text field to numbers
that
would populate the numeric field. Every time I run the update query,
it
wipes out all the numbers in the text field. I must be doing
something
wrong.

In update query, I chose the correct table, then in first row, I
chose,
[numbers], the text field. In update to box I chose [test], the
numeric
field. I run the query and it wipes out all date in the [numbers]
field.

How do I resolve this?

Thanks much for your patience
I have a table in Access. It has a text field filled with numeric
values.
How do I convert the text field to numeric without losing all the
text
data
in it that is numeric?

I appreciate any assistance.

 

[Klatuu]

Try changing it to:
UPDATE test SET test.test = Val([numbers]);
--
Dave Hargis, Microsoft Access MVP

this is the sql statement

UPDATE test SET test.test = [numbers];

I get error saying it can't update due to conversion error.

I tired this with following error message

You have it backwards. If you want the numeric field test updated with
the
value in the numbers:

UPDATE test SET test.test = [numbers];

The way you had it is why the numbers field was blanking out. Test has
no
value in the field, and you were putting the Null value from the test
field
into the numbers field.
--
Dave Hargis, Microsoft Access MVP


:

Here it is Dave.

UPDATE test SET test.numbers = [test];

Post the SQL of your udate query, and I will have a look at it.
--
Dave Hargis, Microsoft Access MVP


:

Dave:

Thanks again.

I have various numbers in a text field - ie. 122 344, 234 666,
etc. -
called [numbers]. I now have a blank numeric field called [test] in
the
table.

I just want to convert all the numbers in the text field to numbers
that
would populate the numeric field. Every time I run the update query,
it
wipes out all the numbers in the text field. I must be doing
something
wrong.

In update query, I chose the correct table, then in first row, I
chose,
[numbers], the text field. In update to box I chose [test], the
numeric
field. I run the query and it wipes out all date in the [numbers]
field.

How do I resolve this?

Thanks much for your patience
I have a table in Access. It has a text field filled with numeric
values.
How do I convert the text field to numeric without losing all the
text
data
in it that is numeric?

I appreciate any assistance.

 

[John W. Vinson]

Hi John:

Thanks for assistance.

See below sample of data

test Number test
985 891 4101

985 845 4028

985 651 4528

985 542 5257

985 542 0064

985 345 1004

936 275 9916

936 275 9893

601 234 3892

507 695 2367

504 888 0912

504 887 6630



All numbers will be in above format. Note: Number col. is a text field and
Test col is a numeric - Long Integer Field. I don't want to lose the data in
the number field. When completed, both field should have the same data in
them.

Not sure how to run the update query to accomplish this.

These ARE NOT NUMBERS, if you ask me.

Numbers do not, and cannot, contain blanks.

If these are telephone numbers, or some other sort of identifier, then a Text
field is the appropriate datatype. you would only convert it to a Number
datatype if you wanted to do arithmatic operations on the values. What
real-life entity do these values represent? What use will you be making of the
number field when you're done? Should the string " 985 891 4101" generate the
number 9,858,914,401? If so, be aware that the largest possible Long Integer
is 2,147,483,647 and your conversion WILL FAIL.

John W. Vinson [MVP]

 

[Frank]

Yes, these are telephone numbers.

These ARE NOT NUMBERS, if you ask me.

Numbers do not, and cannot, contain blanks.

If these are telephone numbers, or some other sort of identifier, then a
Text
field is the appropriate datatype. you would only convert it to a Number
datatype if you wanted to do arithmatic operations on the values. What
real-life entity do these values represent? What use will you be making of
the
number field when you're done? Should the string " 985 891 4101" generate
the
number 9,858,914,401? If so, be aware that the largest possible Long
Integer
is 2,147,483,647 and your conversion WILL FAIL.

John W. Vinson [MVP]

 

[Frank]

Tried this without success.

Try changing it to:
UPDATE test SET test.test = Val([numbers]);
--
Dave Hargis, Microsoft Access MVP

this is the sql statement

UPDATE test SET test.test = [numbers];

I get error saying it can't update due to conversion error.

I tired this with following error message






You have it backwards. If you want the numeric field test updated
with
the
value in the numbers:

UPDATE test SET test.test = [numbers];

The way you had it is why the numbers field was blanking out. Test
has
no
value in the field, and you were putting the Null value from the test
field
into the numbers field.
--
Dave Hargis, Microsoft Access MVP


:

Here it is Dave.

UPDATE test SET test.numbers = [test];

Post the SQL of your udate query, and I will have a look at it.
--
Dave Hargis, Microsoft Access MVP


:

Dave:

Thanks again.

I have various numbers in a text field - ie. 122 344, 234 666,
etc. -
called [numbers]. I now have a blank numeric field called [test]
in
the
table.

I just want to convert all the numbers in the text field to
numbers
that
would populate the numeric field. Every time I run the update
query,
it
wipes out all the numbers in the text field. I must be doing
something
wrong.

In update query, I chose the correct table, then in first row, I
chose,
[numbers], the text field. In update to box I chose [test], the
numeric
field. I run the query and it wipes out all date in the [numbers]
field.

How do I resolve this?

Thanks much for your patience
I have a table in Access. It has a text field filled with numeric
values.
How do I convert the text field to numeric without losing all the
text
data
in it that is numeric?

I appreciate any assistance.

 

[John W. Vinson]

Yes, these are telephone numbers.

Then they should not be converted to any Number type.

What's the meaning of ([Bill Gates' phone number] - [my phone number])/2?
Considerably less useful than ([Bill Gates' income] - [my income]) / 2,
although the latter figure might have some appeal!

What are you trying to ACCOMPLISH here? What's the point of this number field?
Again: *WHY* would you want to do this?

John W. Vinson [MVP]

 

[Frank]

Yep, he's, Gates, not doing too badly for a good ole boy -
Luck of the draw - ie. your phone number.

John:

Let's say the text field with numbers would be used in some sort of
calculation in a text box, and, I wanted, therefore, to convert the text
field, which does not have spaces in the number entries, to a numeric field,
not to exceed 15 digits, what is best way to do this on update query?

Yes, these are telephone numbers.

Then they should not be converted to any Number type.

What's the meaning of ([Bill Gates' phone number] - [my phone number])/2?
Considerably less useful than ([Bill Gates' income] - [my income]) / 2,
although the latter figure might have some appeal!

What are you trying to ACCOMPLISH here? What's the point of this number
field?
Again: *WHY* would you want to do this?

John W. Vinson [MVP]

 

[John W. Vinson]

Yep, he's, Gates, not doing too badly for a good ole boy -
Luck of the draw - ie. your phone number.

John:

Let's say the text field with numbers would be used in some sort of
calculation in a text box, and, I wanted, therefore, to convert the text
field, which does not have spaces in the number entries, to a numeric field,
not to exceed 15 digits, what is best way to do this on update query?

CDec([fieldname]) will convert the number to a Decimal datatype. That will
give you 28 digits precision.

It's still unclear to me what kind of calculations make sense for a
meaningless label such as a telephone number! If you need the area code or
exchange, you can extract them using the Mid() substring function more easily
than any math operations.

John W. Vinson [MVP]

 

[Frank]

Hi John;

Thanks, I'll try your Cdec function. I probably would not use the telephone
field for a function. Just interested in how to convert a text field with
numbers to a numeric field for future references.

Yep, he's, Gates, not doing too badly for a good ole boy -
Luck of the draw - ie. your phone number.

John:

Let's say the text field with numbers would be used in some sort of
calculation in a text box, and, I wanted, therefore, to convert the text
field, which does not have spaces in the number entries, to a numeric
field,
not to exceed 15 digits, what is best way to do this on update query?

CDec([fieldname]) will convert the number to a Decimal datatype. That will
give you 28 digits precision.

It's still unclear to me what kind of calculations make sense for a
meaningless label such as a telephone number! If you need the area code or
exchange, you can extract them using the Mid() substring function more
easily
than any math operations.

John W. Vinson [MVP]