Change existing text data to number data in access 2007

[RickLM]

I need to change existing text data in a field to number data in access 2007
I am trying to move (or just chane) the text data from Fax2 (10 char text
all numbers, no "-", etc) to Fax3 as 10 char number.
I have Fax3 as number field but if I try to update from Fax2 (text field)
using "update Val([Fax2]} I get the cannot update due to incorrect data type
Also if I try to just change Fax2 on table from text to number all valves
are deleted.
I have just enougn VBA understanding to be danagerous. So exact/detailed
help is needed.
Thx in advance.

 

[Linq Adams via AccessMonster.com]

You'll have to wait for someone running 2007 for a definitive answer on this,
as they've changed so much that anything is possible, but changing the
datatype from text to numerical in the table design view *doesn't* delete
values up thru ACC2003, ***unless*** there is, in fact, non-numeric
characters in the fields.

A ten-digit field titled *Fax2* would appear to be a telephone number (area
code plus phone number.) If this is so, why do you need to change it to a
numerical datatype?

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com

 

[WAMTC]

I need to change from text to 10 digit number for use with fax program I'm
using, it does not allow "(xxx) xxx-xxxx" it requires 1234567890 format.
RE deleting data 07 seems to to did... my data is formated 1234567890. so as
you say I ned someone using ACCESS 07.
Thx

 

[Linq Adams via AccessMonster.com]

You originally said

"Fax2 (10 char text all numbers, no "-", etc)"

now you're saying you can't use it because (the fax program)

"does not allow "(xxx) xxx-xxxx" it requires 1234567890 format."

so is Fax2 all numbers or does it have other charactes like (0 and - ?

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com

 

[WAMTC]

As I stated fax2 has just numbers that would be 0-9. and only numbers. fax2
is text data. Not sure what other info i can supply
Thx
Rick

 

[John W. Vinson]

As I stated fax2 has just numbers that would be 0-9. and only numbers. fax2
is text data. Not sure what other info i can supply
Thx
Rick

Please copy and paste examples of the data in Fax1 and Fax2 (change some of
the numbers to 000 if you want to perserve privacy), and indicate what
specific problem you are having. As Linq says, there seem to be some logical
contradictions here!

John W. Vinson [MVP]

 

[WAMTC]

John I have tied to change existing 10 digit text fields (fax1 and fax2) to
number fields by changing the data type from text to number and by using
Val([Fax1]) and Val([fax2]). The results have been the same I get an error
msg stating that data will be deleted if I continue with trying to make the
change.
Fax1 Fax2
1234567890 0987654321
2345678901 1098765432
3456789012 2109876543
I just added a new fax # to the table for a total of 8 fax numbers and when
I changed the data type I got a msg that 7 of the 8 would be deleted. The
only number that was not deleted in the change from tewxt to number was the
one that I added and the existing ones were deleted.
I think that I will just manually reenter the numbers

 

[John W. Vinson]

John I have tied to change existing 10 digit text fields (fax1 and fax2) to
number fields by changing the data type from text to number and by using
Val([Fax1]) and Val([fax2]). The results have been the same I get an error
msg stating that data will be deleted if I continue with trying to make the
change.
Fax1 Fax2
1234567890 0987654321
2345678901 1098765432
3456789012 2109876543
I just added a new fax # to the table for a total of 8 fax numbers and when
I changed the data type I got a msg that 7 of the 8 would be deleted. The
only number that was not deleted in the change from tewxt to number was the
one that I added and the existing ones were deleted.
I think that I will just manually reenter the numbers

STOP!!!

Text is the *CORRECT* datatype for fax numbers.

You want leading zeros. You will lose them with a Number datatype.

You want ten digits. Number/Long Integer fields cannot be bigger than
2147483647, so any fax number in an area code beyond 214 will give an error.

You'll never be doing mathematical operations with fax numbers - just set the
Input Mask to 0000000000 to force entry of all digits, and leave the datatype
as Text.

AND... if you have eight *fields* for fax numbers, you do not have a
relational table; you have a spreadsheet! "Fields are expensive, records are
cheap" - if you have more than one fax number (for a client, say) then you
need *TWO TABLES* in a one to many relationship, with one fax number per
record in the Fax table (in a text field, of course!!)

John W. Vinson [MVP]

 

[WAMTC]

John... THANKS!!
Like I said I jnow just enough to be dangerous...
Rick

John I have tied to change existing 10 digit text fields (fax1 and fax2) to
number fields by changing the data type from text to number and by using
Val([Fax1]) and Val([fax2]). The results have been the same I get an error
msg stating that data will be deleted if I continue with trying to make the
change.
Fax1 Fax2
1234567890 0987654321
2345678901 1098765432
3456789012 2109876543
I just added a new fax # to the table for a total of 8 fax numbers and when
I changed the data type I got a msg that 7 of the 8 would be deleted. The
only number that was not deleted in the change from tewxt to number was the
one that I added and the existing ones were deleted.
I think that I will just manually reenter the numbers

STOP!!!

Text is the *CORRECT* datatype for fax numbers.

You want leading zeros. You will lose them with a Number datatype.

You want ten digits. Number/Long Integer fields cannot be bigger than
2147483647, so any fax number in an area code beyond 214 will give an error.

You'll never be doing mathematical operations with fax numbers - just set the
Input Mask to 0000000000 to force entry of all digits, and leave the datatype
as Text.

AND... if you have eight *fields* for fax numbers, you do not have a
relational table; you have a spreadsheet! "Fields are expensive, records are
cheap" - if you have more than one fax number (for a client, say) then you
need *TWO TABLES* in a one to many relationship, with one fax number per
record in the Fax table (in a text field, of course!!)

John W. Vinson [MVP]