convert per cent text to number

[Guest]

in access query:
i have a text field that express percent as ex. :2.5%
I want to convert this to a number. I've tried changing the underlying
table's (a linked table) field type to numeric but get whole/rounded numbers
even when I attempt to express it with multiple decimal places. How can I
convert by using a query?

 

[Michel Walsh]

It is probably already a number! just formatted to show a % FORMAT, but
format is make-up for what is displayed. The "real" stuff is pr0bably
already a number ( a value between 0 and 1, quite likely). What leaves you
on the impression it is not a number? are the values sorted numerically (1,
2, 3, .., 9, 10, ...) or alphanumerically (1, 10, 11, 12, ...., 19, 2, 20,
21, ..... ) if you sort them?

Vanderghast, Access MVP

 

[John W. Vinson]

in access query:
i have a text field that express percent as ex. :2.5%
I want to convert this to a number. I've tried changing the underlying
table's (a linked table) field type to numeric but get whole/rounded numbers
even when I attempt to express it with multiple decimal places. How can I
convert by using a query?

The default Number datatype is a Long Integer - and integers are by definition
whole numbers with no decimal places. Try changing it to a Double Float
number.

2.5% is just a way of representing the number 0.025, and Access is usually
clever enough to recognize that. If it doesn't, a query updating a (Double,
not long integer!!) field to

Val([textfield]) / 100.

should do the job.

John W. Vinson [MVP]

 

[Guest]

I don't think its a number because when I try a calculation in a query it
won't work.

 

[Guest]

Anytime I use this field in an expression/calculation I get"...to complex"
error. Same result with your suggestion.

in access query:
i have a text field that express percent as ex. :2.5%
I want to convert this to a number. I've tried changing the underlying
table's (a linked table) field type to numeric but get whole/rounded numbers
even when I attempt to express it with multiple decimal places. How can I
convert by using a query?

The default Number datatype is a Long Integer - and integers are by definition
whole numbers with no decimal places. Try changing it to a Double Float
number.

2.5% is just a way of representing the number 0.025, and Access is usually
clever enough to recognize that. If it doesn't, a query updating a (Double,
not long integer!!) field to

Val([textfield]) / 100.

should do the job.

John W. Vinson [MVP]

 

[John W. Vinson]

Anytime I use this field in an expression/calculation I get"...to complex"
error. Same result with your suggestion.

Please open the Table in design view and select this field. What is its
datatype in the second column of the field description? When the field is
selected, what is its size in the uppermost row of the field properties window
on the lower left of the screen?

John W. Vinson [MVP]

 

[Michel Walsh]

Maybe the error is somewhere ELSE in the query.

Have you try a very, very, simple query involving it, such as:


SELECT 100 * fieldNameWithPercent
FROM tableNameHere


If it works in that query, then you can safely assume the error is somewhere
else than with the % value.




If the values is really a string with a % sign in it, a possible solution is
to use something like:


SELECT Eval(Replace( fieldNameHere ,"%","/100.0")) AS Expr1
FROM tableNameHere




Vanderghast, Access MVP