If Statement for numbers

[GoodLife22]

I did a quick search & didn't see anything, sorry if this is a repost. I hope this is an easy one.

I have a varchar field called [CUST_CODE] Each customer has a unique code. Some include numbers, some do not. So the data looks like:

SMS.DELTA.1952
CRY.DOWN.NA
SMS.ALPHA.NA
CRT.ALPHA.5555
CRT.HOUS.NA
ALL.AUST.NA
TCR.ALTA.7412

Each section means something different to my staff. On my main dataentry form in Access I want to add a simple if statement that says:

When a record loads IF the [CUST_CODE] has any numbers in it pop up a message box

So it would look something like:

Private Sub Form_Current()

If
Me.CUST_CODE (includes numbers) then
MsgBox "This is a location only client. Only send product via FedEx"
Else
'Do nothing
End If

End Sub


Hopefully this makes sense. Thank you to anyone who can assist.

 

[TJPoorman]

This function will return true if any of the characters are numeric:

Public Function HasNumbers(strInput As String) As Boolean
Dim i As Integer

For i = 1 To Len(strInput)
    If IsNumeric(Mid(strInput, i, 1)) Then
        HasNumbers = True
        Exit For
    End If
Next i
End Function

 

[CJ_London]

create a function

 function hasNumber(str as string) as Boolean
 dim I as integer
  
 hasnumber=false 
 for I=1 to len(str)
     if isnumeric(str,I,1) then
         hasnumber=true
         exit function
     end if
 next I
 end function

and call it

 If hasnumber(Me.CUST_CODE) then MsgBox "This is a location only client. Only send product via FedEx"

 

[GoodLife22]

Poorman & London. THANK YOU both for the answer. I will try it now and post my results soon. Seriously thank you !!!

 

[GoodLife22]

I just tried this and received an error:

Compile error:
Wrong number of arguments or invalid property assignment

And it highlights the top line in yellow:
Function hasNumber (str As String) as Boolean
and it also highlights the "isnumeric" in blue

 

[CJ_London]

my mistake

should be

isnumeric(mid(str,I,1))

 

[static]

Function hasNumber(str As String) As Boolean
    hasNumber = str Like "*[0-9]*"
End Function

might be quicker.

 

[CJ_London]

good one! you don't even need a function

If Me.CUST_CODE Like "*[0-9]*" then MsgBox "This is a location only client. Only send product via FedEx"

 

[GoodLife22]

my mistake

should be

isnumeric(mid(str,I,1))

This one worked. I am assuming the 'mid' part just means if there is a number anywhere in the field?

 

[GoodLife22]

I went with this for my final line

If Me.CUST_CODE Like "*[0-9]*" then MsgBox "This is a location only client. Only send product via FedEx"

And it worked great. Its clean and simple and easy to understand. Seriously THANK YOU all 3 for your help. This was perfect.

 

[CJ_London]

see also the suggestion by static

I am assuming the 'mid' part just means if there is a number anywhere in the field?

not quite, that is what the function does, it looks at each character in turn. google 'vba mid function' to find out more about what it does