Solved

Val() is returning unexpected results in a query.

Posted on 2008-06-17
25
678 Views
Last Modified: 2008-06-18
I have a field called PartNumber in a query.  Using the query I"m trying to extract the number in front of the text.  When I try to extract the number in the string using the VAL() function, I am getting unexpected results.  It is thinking that the text string of the part number is an exponential number for the numbers with "E" in the string, but for some reason, it is also doing the same to "D".  Funny.  How do I apply the VAL() function without it assuming it is exponential for these parts?

See the data below:

PartNumber            Val(PartNumber)
11AA3                  11
11AA4                  11
11AA34                  11
11AA35                  11
12D10                  120000000000
12D102                  1.2E+103
12D103                  1.2E+104
12DA1                  12
0101A11                  101
0101B1                  101
0101B2                  101
0101B10                  101
0101BB1                  101
0101BB2                  101
0101E1                  1010
0101E9                  101000000000
0101E10                  1010000000000
0
Comment
Question by:shrimpfork
  • 10
  • 8
  • 5
  • +1
25 Comments
 
LVL 75
ID: 21805867
Odd .... but, note the part about HEX - from the Help File:

Val Function
     

Returns the numbers contained in a string as a numeric value of appropriate type.

Syntax

Val(string)

The required string argument is any valid string expression.

Remarks

The Val function stops reading the string at the first character it can't recognize as part of a number. Symbols and characters that are often considered parts of numeric values, such as dollar signs and commas, are not recognized. However, the function recognizes the radix prefixes &O (for octal) and &H (for hexadecimal). Blanks, tabs, and linefeed characters are stripped from the argument.

The following returns the value 1615198:

Val("    1615 198th Street N.E.")

In the code below, Val returns the decimal value -1 for the hexadecimal value shown:

Val("&HFFFF")

Note   The Val function recognizes only the period (.) as a valid decimal separator. When different decimal separators are used, as in international applications, use CDbl instead to convert a string to a number.
0
 
LVL 44

Accepted Solution

by:
GRayL earned 125 total points
ID: 21806016
Try this:


Val(Replace(Replace(PartNumber,"D","A"),"E","B"))
0
 

Author Comment

by:shrimpfork
ID: 21806060
GRayL,
This is exactly what I had to do.  I was looking to see if there was a better was of doing this.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 21806322
I think that is the best mousetrap;-)
0
 

Author Comment

by:shrimpfork
ID: 21806396
GrayL,
I understand the string with the "E" as being exponential, but not the "D".  Do you have any idea why the letter "D" is also resulting as an exponential number?
0
 
LVL 75
ID: 21806406
How do you know that will cover all cases ?

And *why* ... is this happening ?

mx
0
 
LVL 75
ID: 21806424
This

("12D10") =  120000000000

has GOT to be a bug.  D ??????

mx
0
 
LVL 75
ID: 21806449
WOW ...

cdbl("12D10")  and cdec("12D10")  

also returns   120000000000   !!

0
 

Author Comment

by:shrimpfork
ID: 21806514
I know!  Crazy!  I have been fighting this for a while now.  I just have been using the replace function to bandaid.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 21807557
No bug, nothing new. This way it has been "always". Counts for IsNumeric as well.
"D" stands for decimal.

/gustav
Public Function IsDigits( _

  ByVal varExpression As Variant) _

  As Boolean

  

  ' Returns True if varExpression contains digits only.

  '

  ' This is more restrictive than IsNumeric which also accepts

  ' expressions as 2E7 and 3D4 as well as leading and

  ' trailing plus or minus sign or spaces, and hexadecimal or

  ' octal declared numbers as strings like "&H100" and "&O70",

  ' and decimals.

  '

  ' Equivalent ascii value ranges for the allowed characters.

  '   0 to 9: 48-57

  

  Dim strExpression As String

  Dim intLen        As Integer

  Dim intPos        As Integer

  Dim booNonNumeric As Boolean

  

  If IsNumeric(varExpression) Then

    strExpression = CStr(varExpression)

    intLen = Len(strExpression)

    ' Verify each character.

    For intPos = 1 To intLen

      Select Case Asc(Mid(strExpression, intPos, 1))

        Case 48 To 57

          ' Character is a digit.

        Case Else

          ' Character is something else

          booNonNumeric = True

          Exit For

      End Select

    Next

  Else

    ' Return False at once.

    booNonNumeric = True

  End If

  

  IsDigits = Not booNonNumeric

  

End Function

Open in new window

0
 
LVL 75
ID: 21807678
Unfortunately, that is not mentioned in the Help File.  And if it's not a bug, then it's a Flaw - as in inconsistent !!

mx
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 21807758
Oh, it is not Access related, not even VBA. This origins from Basic.

/gustav
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 75
ID: 21807885
A Flaw in by any other name is still a flaw :-)

Basically, it renders the Val function useless.

because *clearly* ... the Val("12D102")  should in No Way return     1.2E+103.

And .. to not mention that in the help file is shameful !!

mx
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 21810546
No, this is just a common misuse of Val because it acts precisely as intended.

However, this cannot be expected to be known by programmers today, thus you are right - the on-line help should spell out the interpretation of <digits>D<digits> and <digits>E<digits> as it does for &H and &O.

Ray's method is probably the closest you can get as a preparation to have Val to function the way the questioneer wants. For generic use it must be expanded, however, to something like this as you probably won't accept a space in the string:

Val(Replace(Replace(Replace(Replace(Replace(strNumber,"D","_"),"E","_"),"&H","_"),"&O","_")," ","_"))

And if a dot should not be read as the decimal separator, one level more:

Val(Replace(Replace(Replace(Replace(Replace(Replace(strNumber,"D","_"),"E","_"),"&H","_"),"&O","_")," ","_"),".","_"))

And if a prefixed minus sign should stop reading:

Val(Replace(Replace(Replace(Replace(Replace(Replace(Replace(strNumber,"D","_"),"E","_"),"&H","_"),"&O","_")," ","_"),".","_"),"-","_"))

This will read digits from position one and forward until any other character than a digit is met.

/gustav
0
 
LVL 75
ID: 21810583
Excerpt from the Help File:

"The Val function stops reading the string at the first character it can't recognize as part of a number."

Wrong.  D and E ... two letters of the alphabet - are 'special cases - as well as &H and #O !!!  That fact along renders Val() useless to me ... and I won't recommend using it anywhere nor will I use it anywhere.  It's a bogus function as far as I'm concerned at this point.  To require all of those Replace functions as a workaround is totally insane - not to mention no doubt a performance hit if used in queries against a lot of records, etc.

But ... if you say this is a 'common' misuse ... then what is the intended use?

mx

0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 21810706
The intended use is to read a possible numeric value of a string and to act as the counterpart to Str().

Str(12.000000) -> " 12"
Str(12.450000) -> " 12.45"
Str(-12.450000) -> "-12.45"
Str(1200000000000000) -> " 12E+15"

Note the leading space for positive values or minus sign for negative values.

These strings will be correctly read by Val.
Also, in a query, Val as a native SQL function is the fastest way to convert a string number to a numeric value

The side effect - to extract the leading value of a mixed string ignoring spaces - is often very handy. But exactly this question demonstrates that you have to be careful and be sure of what kind of input you may expect.
For example, if these partnumbers alway contained two juxtaposed letters (if any), Val would fit the bill perfectly as is.

/gustav
0
 
LVL 75
ID: 21810763
"These strings will be correctly read by Val."

But ... those 'strings' don't contain non-numeric characters - except a dot and minus sign.

Again ... per the Help File:

""The Val function stops reading the string at the first character it can't recognize as part of a number.""

>>As Part of a Number<<

Well ... D and E an not part of a number.  oops ... Houston, we have a problem.

So ... from a reliability standpoint ... I can only see using Val() in the case where you need to convert an actual 'number' to a String - *not* with a 'mixed' string.

This has been very enlightening ...

mx
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 21811021
Yes, the key issue is the missing explanation of this:

> the first character it can't recognize as part of a number.

Except if you know it - and then why look up the help - it is hard to guess that
E is "a part of a number" here:

  " 12E+15"

but not (as an extreme example) here:

  " 12E^15"

/gustav
0
 

Author Comment

by:shrimpfork
ID: 21812325
I would say the 12E15 should be treated differantly than 12E+15 with the Val() statement.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 21812704
But it doesn't.
"12E15", "12E 15" and "12E+15" all leave the same result with Val.
Different, of course, is the result from "12E-15".

/gustav
0
 

Author Comment

by:shrimpfork
ID: 21812758
Mr Data,
Yes, I know.  But if there would be a change, 12E15 vs. 12E+15 should act differently for Val.
Moving on....
0
 
LVL 75
ID: 21814075
Val() = Toast in my book :-)

Badly implemented function.

Oh well ...

mx
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 21814295
Sorry, didn't mean to spoil your days!

/gustav
0
 
LVL 75
ID: 21814481
On the contrary ... it will no doubt save me pain, heartache and suffering in the future.

So ... *thanks* ...

mx
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 21814654
Oh. Great!

/gustav
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now