Convert Percent Value to Number Only

[creativefusion]

Hi All,

I have a list box that contains multiple records with 3 field values.

Value 1 = Integer i.e. 1234
Value 2 = Text "NO NAME"
Value 3 = "87.34%"

On the double click event of the list box, I populate the above three values into seperate text boxes in the user form. These values are used for an update query.

The problem I have is with Value3. If the user does not change the value, my function collects it as Text i.e. "87.34%" instead of 0.8734.

Does anyone have any idea how I can programmatically change this to the value I need to collect for my function?

i.e. My value of 100.00 would be collected as 1. My value of 23.65% would be collected as 0.2365 and so forth.

Thanks in advance.
SJ

 

[vbaInet]

Have a look into the Val() function. You would also need to divide by 100.

 

[creativefusion]

Thanks for the suggestion but unfortunately this val function does not fix my problem.

The code is still collecting "1.84%" when the update button is clicked.

 

[vbaInet]

It depends how it was used. Can you show the function? Also where should this be taken place: " change this to the value I need to collect for my function?"?

 

[Galaxiom]

Try this:

CDec(Left(variable,Len(variable)-1))

 

[creativefusion]

G'idday GalaxiomAtHome,

Works a treat!!

Thanks muchly and enjoy the rest of your weekend.

SJ

 

[lovor]

There is one VERY elegant solution:
use FORMAT function
e.g.
Valuenum3 = Format(Value)
or
Valuenum3 = Format(Value,"General Number")

 

[gemma-the-husky]

interesting

% seems to mess up val()

val ("12.45XY") ---> 12.45
val ("12.45%") ---> type mismatch

I couldn't find a simple way to circumvent this

 

[lovor]

Additionally, you may want to check if result of format function is numeric, if you are converting user-entered value

for example, Format("23%", "General Number") = 0,23, which is a number

but Format("%", "General Number") = "%"
and Format("", "General Number") = ""
which are not numeric
if you check the result afterwards with IsNumeric() function, you should be safe.

I found that Format function works great for many conversions - custom dates etc.

 

[RobinMB]

This thread was helpful to me, so I though I would contribute.
I have good results from nesting Format and Val, like so:
Val(Format(["13.37%"])).
Looks like the 'Format', takes care of the "%" so that the 'Val' can convert to number. Dunno if it is the best approach, but it is another approach.