VAL Function Error

[Nicolas M]

I have been using the VAL function for a couple years with no problem in
Access 2003, but have recently come across a particular sequence of letter
and numbers that causes an error, which I don't understand. Based on the
following input and output:

Original Value Val Result
6240b10043105 6240
6240c10043105 6240
6240D10043105 #Error
6240e10043105 #Error
6240f10043105 6240
6240g10043105 6240
6240d10 6.24E+13
6240d100 6.24E+103
6240d1001 #Error

Why do the original values with 'e' or 'd' in the 5th character error if the
length is greater than 8 characters? As you can, if it is 8 characters it
appears to be interpreting the 'd' or 'e' as "to power of" as see by the
input value '6240d100' and '6240d10'

Is there something about the VAL function that I don't understand or is this
a bug?

Thanks
Nicolas

 

[Jerry Whittle]

I can confirm that it happens in Access 2007. Seems that you stumbled upon
something pretty strange. I think that the e and d represent something like
Expotential and Decimal notation (math wasn't my strong point in high
school). Access will see it as a valid number, and a rather large one at
that! Something like below will trap it.

Debug.Print Val(Replace("6240e10043105","e", "a"))

Of course D is also a problem. You may need to write a convoluted IIf
statement or do a Case in a function.

 

[John Spencer]

You could use an expression like the following:

Val(Replace(Replace([OrigValue],"D","A"),"E","A"))

Val has acted like this since the beginning. It is trying to interpret a
string that is a number value and "6e3" is valid notation for a number as is
"6d3".

You could write a simple custom VBA function to convert the value to a number
and handle it as you wish if you passed in a value that was null (val errors
on nulls) or if the value contained D or E.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Jerry Whittle]

Replace(Replace( I'll need to remember that one.

Thanks John!
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

You could use an expression like the following:

Val(Replace(Replace([OrigValue],"D","A"),"E","A"))

Val has acted like this since the beginning. It is trying to interpret a
string that is a number value and "6e3" is valid notation for a number as is
"6d3".

You could write a simple custom VBA function to convert the value to a number
and handle it as you wish if you passed in a value that was null (val errors
on nulls) or if the value contained D or E.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I can confirm that it happens in Access 2007. Seems that you stumbled upon
something pretty strange. I think that the e and d represent something like
Expotential and Decimal notation (math wasn't my strong point in high
school). Access will see it as a valid number, and a rather large one at
that! Something like below will trap it.

Debug.Print Val(Replace("6240e10043105","e", "a"))

Of course D is also a problem. You may need to write a convoluted IIf
statement or do a Case in a function.

.

 

[John W. Vinson]

Why do the original values with 'e' or 'd' in the 5th character error if the
length is greater than 8 characters? As you can, if it is 8 characters it
appears to be interpreting the 'd' or 'e' as "to power of" as see by the
input value '6240d100' and '6240d10'

Is there something about the VAL function that I don't understand or is this
a bug?

This is a holdover to the OLD days... back in 1968 when I took my first
FORTRAN class I learned the "scientific notation" syntax where 6240E10 was
interpreted as 6240 * 10^10. E was used for "Float" numbers, D for "Double
Precision" numbers; Access still has Float and Double number datatypes, and
the E and D notation is still recognized.

The reason you're getting an error is that a Double number has a range up to
10^318 (if I recall aright) - that's a monstrously huge number even for a
cosmologist, but it's the limit. 10^1000 is bigger than a Double can hold, so
you're getting the error.

Jeff's suggestion about replacing the D or E is your best bet.

 

[John W. Vinson]

Jeff's suggestion about replacing the D or E is your best bet.

oops... *John's* suggestion. Sorry John.

 

[Nicolas M]

The Replace works perfectly! Thanks so much for your help.
Nicolas

You could use an expression like the following:

Val(Replace(Replace([OrigValue],"D","A"),"E","A"))

Val has acted like this since the beginning. It is trying to interpret a
string that is a number value and "6e3" is valid notation for a number as is
"6d3".

You could write a simple custom VBA function to convert the value to a number
and handle it as you wish if you passed in a value that was null (val errors
on nulls) or if the value contained D or E.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I can confirm that it happens in Access 2007. Seems that you stumbled upon
something pretty strange. I think that the e and d represent something like
Expotential and Decimal notation (math wasn't my strong point in high
school). Access will see it as a valid number, and a rather large one at
that! Something like below will trap it.

Debug.Print Val(Replace("6240e10043105","e", "a"))

Of course D is also a problem. You may need to write a convoluted IIf
statement or do a Case in a function.

.

 

[Nicolas M]

Thanks for the detailed explanation. The Replace suggestion worked perfectly.
Nicolas