Alphanumeric Validation Rule

[Access Greenhorn]

I have a field that consists of an alphanumeric string that serves as a
barcode. It's Input Mask looks like this 00"-V"0\T0\R0\-099\-0. A barcode
will therefore look like this 06-V1T3R4-235-8. Is there a way to set a
Validation Rule so that the second last number (235 in above example) must be

 

[John Nurick]

I'm pretty sure you can't do it in a field validation rule. It may be
possible to build a suitable expression in a table validation rule,
using functions like Mid() and InStr(), but it would be quite fiddly to
extract between one and three digits. Assuming you're using forms for
all data entry and editing, it's probably simplest to do this validation
in the BeforeUpdate event of the textbox that's displaying the string.


On Fri, 12 May 2006 09:58:02 -0700, Access Greenhorn <Access

 

[Tim Ferguson]

=?Utf-8?B?QWNjZXNzIEdyZWVuaG9ybg==?= <Access
(e-mail address removed)> wrote in

will therefore look like this 06-V1T3R4-235-8. Is there a way to set
a Validation Rule so that the second last number (235 in above
example) must be

Not tested, but this should be something like it:


( mid$(myfield,11,3) like "[0-2][0-9][0-9]"
OR mid$(myfield,11,3) like "3[0-5][0-9]"
OR mid$(myfield,11,3) like "36[0-4]"
)
AND mid$(myfield,11,3) Not like "00[01]"

Hope that helps


Tim F

 

[Access Greenhorn]

I tried Tim but I got a validation rule violation error message with any
number I tried. To make sure I did it correctly, I put

(Mid$("ItemCode",11,3) Like "[0-2][0-9][0-9]" Or Mid$("ItemCode",11,3) Like
"3[0-5][0-9]" Or Mid$("ItemCode",11,3) Like "36[0-4]") And
Mid$("ItemCode",11,3) Not Like "00[01]"

into the validation rule field property for the field 'ItemCode'. And, so I
learn, what purpose does the 11,3 appear?

Thanks for your help.

=?Utf-8?B?QWNjZXNzIEdyZWVuaG9ybg==?= <Access
(e-mail address removed)> wrote in

will therefore look like this 06-V1T3R4-235-8. Is there a way to set
a Validation Rule so that the second last number (235 in above
example) must be

Not tested, but this should be something like it:


( mid$(myfield,11,3) like "[0-2][0-9][0-9]"
OR mid$(myfield,11,3) like "3[0-5][0-9]"
OR mid$(myfield,11,3) like "36[0-4]"
)
AND mid$(myfield,11,3) Not like "00[01]"

Hope that helps


Tim F

 

[Tim Ferguson]

=?Utf-8?B?QWNjZXNzIEdyZWVuaG9ybg==?=

(Mid$("ItemCode",11,3) Like "[0-2][0-9][0-9]" Or Mid$("ItemCode",11,3)
Like "3[0-5][0-9]" Or Mid$("ItemCode",11,3) Like "36[0-4]") And
Mid$("ItemCode",11,3) Not Like "00[01]"

or formatted for human consumption

(
Mid$("ItemCode",11,3) Like "[0-2][0-9][0-9]" Or
Mid$("ItemCode",11,3) Like "3[0-5][0-9]" Or
Mid$("ItemCode",11,3) Like "36[0-4]"
)
And Mid$("ItemCode",11,3) Not Like "00[01]"

into the validation rule field property for the field 'ItemCode'.
And, so I learn, what purpose does the 11,3 appear?

To take the last question first; the Mid$() function takes three
arguments: the string, the position to start from, and the number of
characters to take. The expression

Mid$("ItemCode", 11, 3)

therefore evaluates to nothing, since "ItemCode" does not have eleven
characters in it. I think you'll need something like

Mid$([ItemCode],11,3)

and, if [ItemCode] is in the form of "06-V1T3R4-235-8", it should return
"235" which is what you want.

Like I said, I have not tested it!

Best of luck


Tim F

 

[Douglas J. Steele]

Mid$("ItemCode",11,3) is a way of retrieving the 3 characters that start at
position 11 of the string. However, since you've put the literal string
"ItemCode" there, and since there are only 8 characters in that string , you
won't get anything.

If you want to refer to a field, try Mid$([ItemCode],11,3)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I tried Tim but I got a validation rule violation error message with any
number I tried. To make sure I did it correctly, I put

(Mid$("ItemCode",11,3) Like "[0-2][0-9][0-9]" Or Mid$("ItemCode",11,3)
Like
"3[0-5][0-9]" Or Mid$("ItemCode",11,3) Like "36[0-4]") And
Mid$("ItemCode",11,3) Not Like "00[01]"

into the validation rule field property for the field 'ItemCode'. And, so
I
learn, what purpose does the 11,3 appear?

Thanks for your help.

=?Utf-8?B?QWNjZXNzIEdyZWVuaG9ybg==?= <Access
(e-mail address removed)> wrote in

will therefore look like this 06-V1T3R4-235-8. Is there a way to set
a Validation Rule so that the second last number (235 in above
example) must be
1 and <365?

Not tested, but this should be something like it:


( mid$(myfield,11,3) like "[0-2][0-9][0-9]"
OR mid$(myfield,11,3) like "3[0-5][0-9]"
OR mid$(myfield,11,3) like "36[0-4]"
)
AND mid$(myfield,11,3) Not like "00[01]"

Hope that helps


Tim F

 

[Access Greenhorn]

Tim, Douglas:

My bad folks, I should know better than to refer to a field without square
brackets. That being said, I tried the code again with the square brackets
and same result.

Jamie:

I tried the following four:

[ItemCode] LIKE '%-%-[1-9]-%'
OR [ItemCode] LIKE '%-%-[1-9][0-9]-%'
OR [ItemCode] LIKE '%-%-[1-2][0-9][0-9]-%'
OR [ItemCode] LIKE '%-%-3[0-5][0-9]-%'
OR [ItemCode] LIKE '%-%-36[0-5]-%'
OR [ItemCode] LIKE '*-*-[1-9]-*'
OR [ItemCode] LIKE '*-*-[1-9][0-9]-*'
OR [ItemCode] LIKE '*-*-[1-2][0-9][0-9]-*'
OR [ItemCode] LIKE '*-*-3[0-5][0-9]-*'
OR [ItemCode] LIKE '*-*-36[0-5]-*'
AND [ItemCode] <> '%-%-[1-9]-%'
AND [ItemCode] <> '%-%-[1-9][0-9]-%'
AND [ItemCode] <> '%-%-[1-2][0-9][0-9]-%'
AND [ItemCode] <> '%-%-3[0-5][0-9]-%'
AND [ItemCode] <> '%-%-36[0-5]-%'
AND [ItemCode] <> '*-*-[1-9]-*'
AND [ItemCode] <> '*-*-[1-9][0-9]-*'
AND [ItemCode] <> '*-*-[1-2][0-9][0-9]-*'
AND [ItemCode] <> '*-*-3[0-5][0-9]-*'
AND [ItemCode] <> '*-*-36[0-5]-*'

Same as above but with [ItemCode] replaced with ItemCode.

ItemCode LIKE '%-%-[1-9]-%'
OR ItemCode LIKE '%-%-[1-9][0-9]-%'
OR ItemCode LIKE '%-%-[1-2][0-9][0-9]-%'
OR ItemCode LIKE '%-%-3[0-5][0-9]-%'
OR ItemCode LIKE '%-%-36[0-5]-%'

Same as above but with ItemCode replaced with [ItemCode]

All returned the same error message. And again, so I learn what did you mean
by
'implementation independent'?

Thanks all

 

[Tim Ferguson]

I can't see that square brackets would make any difference. If you've
named your columns according to standards you shouldn't need them.

Should do, but the Access UI doesn't agree with you and has a habit of
putting quote marks round anything that doesn't move.

All the best


Tim F