If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
VAL Function Error
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 |
#2
|
|||
|
|||
VAL Function Error
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. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Nicolas M" wrote: 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 |
#3
|
|||
|
|||
VAL Function Error
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 wrote: 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. |
#4
|
|||
|
|||
VAL Function Error
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. "John Spencer" wrote: 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 wrote: 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. . |
#5
|
|||
|
|||
VAL Function Error
On Thu, 3 Dec 2009 06:17:01 -0800, Nicolas M
wrote: 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 [MVP] |
#6
|
|||
|
|||
VAL Function Error
On Thu, 03 Dec 2009 10:26:42 -0700, John W. Vinson
wrote: Jeff's suggestion about replacing the D or E is your best bet. oops... *John's* suggestion. Sorry John. -- John W. Vinson [MVP] |
#7
|
|||
|
|||
VAL Function Error
The Replace works perfectly! Thanks so much for your help.
Nicolas "John Spencer" wrote: 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 wrote: 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. . |
#8
|
|||
|
|||
VAL Function Error
Thanks for the detailed explanation. The Replace suggestion worked perfectly.
Nicolas "John W. Vinson" wrote: On Thu, 3 Dec 2009 06:17:01 -0800, Nicolas M wrote: 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 [MVP] . |
Thread Tools | |
Display Modes | |
|
|