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

[Jamie Collins]

The Val() function will convert a text string which can be interpreted as a
number to a number

Specifically, a FLOAT (Double) value e.g.

SELECT TYPENAME(VAL(0))

returns 'Double'.

You'll need to decide on the datatype of the number field first: do these
numbers havedecimalplaces? 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?

....so if the OP decides that they *do* need more than fifteen digits
of precision, using the VAL() expression could result is a loss of
precision e.g.

SELECT 76543210987654329 - 76543210987654320 AS
decimal_result_correct,
VAL(76543210987654329) - VAL(76543210987654320) as
float_result_incorrect

So DECIMAL would be my preference (up to 28 digits of precision,
supports decimal values, rounds by truncation not banker's, etc).
Sadly, the CDEC() expression remains broken in Jet, but an implicit
cast to DECIMAL may be possible, noting that Jet uses the DECIMAL type
natively for values within certain ranges e.g. this works:

SELECT '76543210987654329' + 0.1 - 0.1 AS decimal_value,
TYPENAME('76543210987654329' + 0.1 - 0.1) AS decimal_type_name

returns 'Decimal'.

Jamie.

--

 

[Frank]

Hi Jamie:

The numbers will always be eleven or less digits without spaces. There will
never be a decimal in them nor will they ever exceed two billion in number.

What should I do now?

 

[Jamie Collins]

The numbers will always be eleven or less digits without spaces. There will
never be a decimal in them nor will they ever exceed two billion in number.

What should I do now?

A modern billion has nine zeros (the old billion -- the old British
one anyhow -- had 12 zeros), therefore even the smallest 11 digit
number be greater than two billion in *value*.

Type the column as DECIMAL, precision=11, scale=0, and leave the rest
to implicit casting e.g.

CREATE TABLE Test (text_col VARCHAR(11))
;
INSERT INTO Test (text_col) VALUES ('000')
;
INSERT INTO Test (text_col) VALUES ('')
;
INSERT INTO Test (text_col) VALUES (NULL)
;
INSERT INTO Test (text_col) VALUES (' ')
;
INSERT INTO Test (text_col) VALUES ('Hello world')
;
INSERT INTO Test (text_col) VALUES ('12345678901')
;
INSERT INTO Test (text_col) VALUES ('-99')
;
INSERT INTO Test (text_col) VALUES ('(55)')
;
INSERT INTO Test (text_col) VALUES ('(-55)')
;
INSERT INTO Test (text_col) VALUES ('(-22)')
;
INSERT INTO Test (text_col) VALUES ('1E9')
;
INSERT INTO Test (text_col) VALUES ('123.456')
;
ALTER TABLE Test ADD
decimal_col DECIMAL(11, 0) DEFAULT 0 NOT NULL)
;
UPDATE Test
SET decimal_col = text_col
WHERE ISNUMERIC(text_col)
;

[Aside: this results in a major inconsistency: the DECIMAL column is
NOT NULL (Required = yes) but it still contains NULL values. Serious
engine error...?]

Jamie.

--

 

[Frank]

Thanks a bunch Jamie.

Still digesting your comprehensive email. Concerning the billions. I thought
you were talking about billions of records instead of integers. Sorry for
mishap.

Your solution appears to work. Thanks again for your most helpful incites
and professional assistance.

The numbers will always be eleven or less digits without spaces. There
will
never be a decimal in them nor will they ever exceed two billion in
number.

What should I do now?

A modern billion has nine zeros (the old billion -- the old British
one anyhow -- had 12 zeros), therefore even the smallest 11 digit
number be greater than two billion in *value*.

Type the column as DECIMAL, precision=11, scale=0, and leave the rest
to implicit casting e.g.

CREATE TABLE Test (text_col VARCHAR(11))
;
INSERT INTO Test (text_col) VALUES ('000')
;
INSERT INTO Test (text_col) VALUES ('')
;
INSERT INTO Test (text_col) VALUES (NULL)
;
INSERT INTO Test (text_col) VALUES (' ')
;
INSERT INTO Test (text_col) VALUES ('Hello world')
;
INSERT INTO Test (text_col) VALUES ('12345678901')
;
INSERT INTO Test (text_col) VALUES ('-99')
;
INSERT INTO Test (text_col) VALUES ('(55)')
;
INSERT INTO Test (text_col) VALUES ('(-55)')
;
INSERT INTO Test (text_col) VALUES ('(-22)')
;
INSERT INTO Test (text_col) VALUES ('1E9')
;
INSERT INTO Test (text_col) VALUES ('123.456')
;
ALTER TABLE Test ADD
decimal_col DECIMAL(11, 0) DEFAULT 0 NOT NULL)
;
UPDATE Test
SET decimal_col = text_col
WHERE ISNUMERIC(text_col)
;

[Aside: this results in a major inconsistency: the DECIMAL column is
NOT NULL (Required = yes) but it still contains NULL values. Serious
engine error...?]

Jamie.

 

[Frank]

One last thing for your information.

I cut and pasted many of the numbers in the text col. from one database to
another rather than individually entering them into the subject database.
Thus, when trying to convert the text field to numeric, Access gives a
"type" error. Evidently, Access conversions from text to numeric is not
recognized when running the Update Query if you cut and past text data from
one field to another rather than hand entering it.. When hand entering data,
everything works perfectly.

Comments?

The numbers will always be eleven or less digits without spaces. There
will
never be a decimal in them nor will they ever exceed two billion in
number.

What should I do now?

A modern billion has nine zeros (the old billion -- the old British
one anyhow -- had 12 zeros), therefore even the smallest 11 digit
number be greater than two billion in *value*.

Type the column as DECIMAL, precision=11, scale=0, and leave the rest
to implicit casting e.g.

CREATE TABLE Test (text_col VARCHAR(11))
;
INSERT INTO Test (text_col) VALUES ('000')
;
INSERT INTO Test (text_col) VALUES ('')
;
INSERT INTO Test (text_col) VALUES (NULL)
;
INSERT INTO Test (text_col) VALUES (' ')
;
INSERT INTO Test (text_col) VALUES ('Hello world')
;
INSERT INTO Test (text_col) VALUES ('12345678901')
;
INSERT INTO Test (text_col) VALUES ('-99')
;
INSERT INTO Test (text_col) VALUES ('(55)')
;
INSERT INTO Test (text_col) VALUES ('(-55)')
;
INSERT INTO Test (text_col) VALUES ('(-22)')
;
INSERT INTO Test (text_col) VALUES ('1E9')
;
INSERT INTO Test (text_col) VALUES ('123.456')
;
ALTER TABLE Test ADD
decimal_col DECIMAL(11, 0) DEFAULT 0 NOT NULL)
;
UPDATE Test
SET decimal_col = text_col
WHERE ISNUMERIC(text_col)
;

[Aside: this results in a major inconsistency: the DECIMAL column is
NOT NULL (Required = yes) but it still contains NULL values. Serious
engine error...?]

Jamie.