Determine in a query if a string has non numeric-characters

Posted on 2014-08-13
Last Modified: 2014-08-13
I have a table wich contains identification strings. I want to use a query to identify those which have any non-numeric characters in them. Examples of them are:


I tried a few things like

Expr1: IIf(IsError([CLIENTID]*1),99,1) and I also tried Expr1: IIf(IsError([CLIENTID]*1),True,False)

That gives me 1 or false respectively or #Error

I don't want the #Error , because I want to update another column with the result.

Please help.
Question by:Fritz Paul
    LVL 29

    Accepted Solution

    A couple of suggestions with some caveats:
    Iif(IsNumeric([ClientID]),True, False)  ---  if clientID begins with as space, it will still see it as a number.
    Iif(Len([ClientID])=Len(Val(ClientID]),True,False)  ---  if clientID has a period, it will still see it as a number.
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    Just to be clear on your requirements:
    "Determine in a query if a string has non numeric-characters"
    " I want to update another column with the result."

    Exactly what do you want to see in the new filed for each of your sample values there?

    Author Closing Comment

    by:Fritz Paul
    Thank you very much. That really helps a lot. Works exactly in all my cases and I am going to use both just for in case.

    Author Comment

    by:Fritz Paul
    Thanks Jeffrey,
    I appreciate you help.
    I have managed now, but what I wanted was in the new column to have a date. Either today or a date derived from the CLIENTID.
    Thanks again.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    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

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    779 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

    16 Experts available now in Live!

    Get 1:1 Help Now