Can Val() be trusted? (1 Viewer)

[JANR]

I have come across a weird result by using Val().

ex:

Val("12535 A1") results in 12535 which is what I expect but if I use

Val("12353 D1") or Val("12353 E1") I get 123530

All other Letters Val("12353 ?1) gets the correct result, any thoughts on this?

JR

 

[Rabbie]

12353E1 is a valid way of writing 123530 in scientific notation where the part after the E is exponent or power of 10 to be applied to the portion before. The VAL function ignores spacs so that is why it is evaluating 12353 E1 as it does. Perhaps someone else can let you know why 12353 D1 is giving the same result

 

[Brianwarnock]

I , and I'm sure many others, did not realise that could happen, I wonder if I have left code somewhere waiting to go wrong.

Brian

 

[DCrake]

Hey Brian 1 more post then you hit the big 8000.....

 

[Brianwarnock]

Hey Brian 1 more post then you hit the big 8000.....

Ok I can't resist it nor stand the suspense so here we go.

While we are on the lighter side why are our jelloes now spaced out?

Brian

 

[JANR]

Thanks Rabbi for the answer, and yes this was just pure luck I stumbled on this as I wanted in my db to extract the number portion from a sting and the D1 part happend to be a storage place.

The easy fix was of course to use Left([field],5).

This is perhaps a lesson to not take everything at facevalue

JR

 

[Brianwarnock]

This is perhaps a lesson to not take everything at facevalue

JR

I think that this could have been a difficult 1 to pick up but does illustrate the need for designed testing. It worries me that so many posters on here are using live data to work with even for complex and difficult to check problem solving.

Brian

 

[gemma-the-husky]

surprising result

of course left(string,5) is only OK if you know 5 chars is what you have

 

[vbaInet]

Rabbie is on point there. E and D are recognised as symbols for exponentiation. Now, because the Val() function trims off spaces, if you had "12345 E 1" it will still be treated as an exponential. The only way I know of is to replace "E" with "Ex" for example.

 

[Galaxiom]

For the sake of those who come later:

Return an unknown number of numeric characters that precede the first non-numeric.

n = Len(somestring)
For i = 1 to n
   If Not IsNumeric(Mid(somestring, i, 1 )) Then
      Result = Left(somestring, i - 1 )
      Exit For
   End If
Next
 
If Len(Result) = 0 Then     [COLOR=seagreen]' test no results and return whole string[/COLOR]
   Result = somestring          [COLOR=seagreen]' adjust to suit your needs[/COLOR]
End If
 
[COLOR=seagreen]' Add code to convert to number type if you want[/COLOR]

 

[JANR]

surprising result

of course left(string,5) is only OK if you know 5 chars is what you have

In my case 5 numbers will "always" be the case, this number increases with 700 each year so I will be fine until year 2130, then it will break.

JR

 

[vbaInet]

Here's how I would do it:

Val(Replace(Replace("12353 E1", "e", "ex"), "d", "dx"))

 

[gemma-the-husky]

oince you know its an issue you can fix it, though - but its an insidious "featutre" if you aren't expecting it, isn't it?