Alphanumeric Validation Rule

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
=?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
Click to expand...

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 said:
=?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
Click to expand...

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
Click to expand...
 
=?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]"
Click to expand...

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?
Click to expand...

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

(no private e-mails, please)


Access Greenhorn said:
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 said:
=?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?
Click to expand...

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
Click to expand...
Click to expand...
 
Tim said:
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.
Click to expand...

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 said:
My bad folks, I should know better than to refer to a field without square
brackets.
Click to expand...

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:
Click to expand...

Four what? That's one validation rule!
All returned the same error message.
Click to expand...

What was the error message?
what did you mean by 'implementation independent'?
Click to expand...

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 said:
I tried the following four
Click to expand...

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.

--
 
Jamie said:
Here's some VBA code to create a test DB and table then try the rule:
Click to expand...

An this is kinder on the google wordwrap:

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
 
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.
Click to expand...

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
 
Back
Top