If Statement for numbers (1 Viewer)

GoodLife22 · Nov 11, 2016

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 · Nov 11, 2016

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

Code:

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 · Nov 11, 2016

create a function

Code:

 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

Code:

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

GoodLife22 · Nov 11, 2016

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

GoodLife22 · Nov 11, 2016

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 · Nov 11, 2016

my mistake

should be

isnumeric(mid(str,I,1))

static · Nov 11, 2016

Code:

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

might be quicker.

CJ_London · Nov 11, 2016

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 · Nov 11, 2016

CJ_London said:
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 · Nov 11, 2016

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 · Nov 11, 2016

If Statement for numbers (1 Viewer)

GoodLife22

Registered User.

TJPoorman

Registered User.

CJ_London

Super Moderator

GoodLife22

Registered User.

GoodLife22

Registered User.

CJ_London

Super Moderator

static

Registered User.

CJ_London

Super Moderator

GoodLife22

Registered User.

GoodLife22

Registered User.

CJ_London

Super Moderator

Similar threads

Users who are viewing this thread