Results 1 to 7 of 7

Thread: Need advice

  1. #1
    Join Date
    Jun 2005

    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


  2. #2
    Join Date
    Nov 2004
    out on a limb
    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
    Madison, WI
    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
    Adelaide, South Australia
    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
    Thanks a lot guys, this will definitely help.

  6. #6
    Join Date
    Jun 2005
    Richmond, Virginia USA
    Provided Answers: 19

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



    will evaluate to a number if

    X is 123ABC.


    X is ABC123

    it will not evaluate to a number.


    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-24-09 at 23:28.
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

  7. #7
    Join Date
    Jun 2005
    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