• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

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

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.
Fritz Paul
Fritz Paul
  • 2
1 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.
Jeffrey CoachmanCommented:
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?
Fritz PaulAuthor Commented:
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.
Fritz PaulAuthor Commented:
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.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now