convert per cent text to number

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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
 
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?
Click to expand...

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]
 
Anytime I use this field in an expression/calculation I get"...to complex"
error. Same result with your suggestion.
John W. Vinson said:
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?
Click to expand...

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]
Click to expand...
 
Anytime I use this field in an expression/calculation I get"...to complex"
error. Same result with your suggestion.
Click to expand...

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]
 
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
 
Back
Top