Alphanumeric Validation Rule

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

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

"Jamie Collins" wrote in
oups.com:

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.


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