If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
Alphanumeric Validation Rule
"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 |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Validation Rule not working in table | jsccorps | Using Forms | 2 | February 15th, 2006 08:21 PM |
Validation - How to identify records that fail a rule | Mark.T | General Discussion | 5 | January 20th, 2006 03:29 AM |
validation rule question | Code Agent | General Discussion | 6 | November 4th, 2005 07:42 AM |
Validation rule | hughess7 | General Discussion | 3 | August 4th, 2005 07:28 PM |
Validation Rule question | Lori | Using Forms | 1 | July 20th, 2004 06:32 PM |