string and char

[James]

Hi: Everybody

I have a lot of ProductID, the format is: first 3 are alphabatics, rest of 3
are numbers, such as: ABC123

I want to check user inputs correctly by using VBA code, what is the
function to pick up any one of the character in the ProductID? How to make
sure the character is alphabatic or number?

Thanks. Appreciate it.

James

 

[Juan M Afan de Ribera]

Hi, try this code

If Me.ProductID Like "[A-Z][A-Z][A-Z]###" Then
MsgBox "OK"
Else
MsgBox "Try again"
End If

HTH

 

[Marshall Barton]

I have a lot of ProductID, the format is: first 3 are alphabatics, rest of 3
are numbers, such as: ABC123

I want to check user inputs correctly by using VBA code, what is the
function to pick up any one of the character in the ProductID? How to make
sure the character is alphabatic or number?

You can use the Mid function to look at any subset of the
characters in a string:

If Mid(Me.ProductID, 3, 1) = "C" Then

There's also the Left and Right functions that can be used
in this kind of operation.

OTOH, if all you want to do is check for alpha and numeric,
you can use the Like operator with an appropriate
combination of wildcards:

If Me.ProductID Like "[A-Z][A-Z][A-Z]###" Then
' it's ok
Else
' bad data
End If

 

[James]

Thanks Guys, it is great.

(I spent 1 hrs last night, and made some "For loops", finally didn't get
anything!).

Thanks.

James

I have a lot of ProductID, the format is: first 3 are alphabatics, rest of 3
are numbers, such as: ABC123

I want to check user inputs correctly by using VBA code, what is the
function to pick up any one of the character in the ProductID? How to make
sure the character is alphabatic or number?

You can use the Mid function to look at any subset of the
characters in a string:

If Mid(Me.ProductID, 3, 1) = "C" Then

There's also the Left and Right functions that can be used
in this kind of operation.

OTOH, if all you want to do is check for alpha and numeric,
you can use the Like operator with an appropriate
combination of wildcards:

If Me.ProductID Like "[A-Z][A-Z][A-Z]###" Then
' it's ok
Else
' bad data
End If