Alphanumeric Validation Rule

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
1 and 365?

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
wrote:

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
1 and 365?

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

=?Utf-8?B?QWNjZXNzIEdyZWVuaG9ybg==?= Access
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

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.

"Tim Ferguson" wrote:

=?Utf-8?B?QWNjZXNzIEdyZWVuaG9ybg==?= Access
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

=?Utf-8?B?QWNjZXNzIEdyZWVuaG9ybg==?=
wrote in
:

(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

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
http://I.Am/DougSteele
(no private e-mails, please)


"Access Greenhorn" wrote in
message ...
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.

"Tim Ferguson" wrote:

=?Utf-8?B?QWNjZXNzIEdyZWVuaG9ybg==?= Access
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

Tim Ferguson wrote:
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.

I don't think we have enough information about the rest of the pattern
to be bale to know we can identify the value to be tested. For example,
Tim's had to assume the 'number' to validate will always start at
position 11.

Although the OP's rule 'second last number' is possible, I suspect it
is not the complete truth either g.

Therefore, I'm going to assume the pattern of the hyphens will
determine the position of the 'number'.

Here's a break down of my proposed solution, using the 'ANSI mode'
wildcard character '%'.

Value is between 1 and 9 inclusive:
item_code LIKE '%-%-[1-9]-%'

Value is between 10 and 99 inclusive:
item_code LIKE '%-%-[1-9][0-9]-%'

Value is between 100 and 299 inclusive:
item_code LIKE '%-%-[1-2][0-9][0-9]-%'

Value is between 300 and 359 inclusive:
item_code LIKE '%-%-3[0-5][0-9]-%'

Value is between 360 and 365 inclusive:
item_code LIKE '%-%-36[0-5]-%'

In full:

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

Or to be implementation-indepenedent, as we should always be:

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

Jamie.

--

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

Access Greenhorn wrote:
My bad folks, I should know better than to refer to a field without square
brackets.

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.

I tried the following four:

Four what? That's one validation rule!

All returned the same error message.

What was the error message?

what did you mean by 'implementation independent'?

Essentially, the wildcard character. In 'ANSI mode' (e.g. ADO) it is %
and in 'prehistoric mode' - sorry, can't think of a non-pejorative term
right now g - (e.g. DAO) it is *. The diligent profession codes VIEWs
('queries'), PROCEDUREs ('parameter queries'), CHECK CONSTRAINTs
('Validation Rules') etc to handle both modes.

Jamie.

--

Access Greenhorn wrote:
I tried the following four

Here's some VBA code to create a test DB and table then try the rule:

Sub test10()

Dim cat As Object
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb;"

With .ActiveConnection
.Execute _
"CREATE TABLE Test10 ( item_code VARCHAR(24) NOT NULL, CONSTRAINT
item_code__pattern" & _
" CHECK ( item_code LIKE '%-%-[1-9]-%' OR item_code LIKE
'%-%-[1-9][0-9]-%' OR item_code" & _
" LIKE '%-%-[1-2][0-9][0-9]-%' OR item_code LIKE
'%-%-3[0-5][0-9]-%' OR item_code" & _
" LIKE '%-%-36[0-5]-%' OR item_code LIKE '*-*-[1-9]-*' OR item_code
LIKE '*-*-[1-9][0-9]-*'" & _
" OR item_code LIKE '*-*-[1-2][0-9][0-9]-*' OR item_code LIKE
'*-*-3[0-5][0-9]-*'" & _
" OR item_code LIKE '*-*-36[0-5]-*' AND item_code '%-%-[1-9]-%'
AND item_code" & _
" '%-%-[1-9][0-9]-%' AND item_code '%-%-[1-2][0-9][0-9]-%'
AND item_code " & _
" '%-%-3[0-5][0-9]-%' AND item_code '%-%-36[0-5]-%' AND
item_code '*-*-[1-9]-*'" & _
" AND item_code '*-*-[1-9][0-9]-*' AND item_code
'*-*-[1-2][0-9][0-9]-*' AND" & _
" item_code '*-*-3[0-5][0-9]-*' AND item_code '*-*-36[0-5]-*'
) );"

' Should pass
.Execute _
"INSERT INTO Test10 (item_code) VALUES ('06-V1T3R4-235-8');"

' Should fail
.Execute _
"INSERT INTO Test10 (item_code) VALUES ('06-V1T3R4-366-8');"

End With
Set .ActiveConnection = Nothing
End With
End Sub


HTH,
Jamie.

--