Results 1 to 7 of 7

Thread: Need advice

  1. #1
    Join Date
    Jun 2005
    Posts
    36

    Exclamation Unanswered: Need advice

    Hi there, here is what I need advice on:

    I have a text box and I need to perform a comparison between the numbers keyed in and a reference no., the thing is that this same text box some times may also have only letters.

    1st. How can I tell if the text box contains a number or a word? (there will never be a case where the input data be alphanumeric)

    2nd. Is the use of Val() the rigth way of converting text to number for the comparison?

    I must do this within VB

    thanks
    Carlos

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    lots of permutations
    test if the value is numeric (use the isnumeric function) would I think be the best alternative
    you may need to also use NZ to test for null values
    unfortunately I don't think regular expressions are available in Access VBA
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    2nd. Is the use of Val() the rigth way of converting text to number for the comparison?

    - I thought it was Cint(...) but maybe I'm wrong.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    1. If IsNumeric(X) Then....

    2. There are lots of type conversions that would work. CDbl(), CInt(), CLng(), CCur() to name a few. Val() might work too.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Jun 2005
    Posts
    36
    Thanks a lot guys, this will definitely help.

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Val(X)

    will convert the text X to a number if the leading character(s) are digits.

    Hence

    Val(X)

    will evaluate to a number if

    X is 123ABC.

    If

    X is ABC123

    it will not evaluate to a number.

    IsNumeric(X)

    will evaluate to False unless each character is a digit. This sounds like what the OP needs.

    Also, unless each character is a digit, the functions CInt, CDbl, and CLng will all throw a Type Mismatch error (Error 13)!

    Also, the OP should note that assuming that the textbox will never be alpha/numeric is very dangerous. IF you can think of it, some user will do it!
    Last edited by Missinglinq; 06-25-09 at 00:28.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    Join Date
    Jun 2005
    Posts
    36
    Thanks Missinglinq, I will keep the alpha/numeric possibility in mind.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •