CODEWITHSUNDEEP
ms-access
Vince L
Vince L

Reputation: 33

Maximum length of Access validation rule?

I am trying to create a validation rule in access. I would like to limit the entered character combinations only to the existing ISO 2 digit country codes:

"AF" or "AX" or "AL" or "DZ" or "AS" or "AD" or "AO" or "AI" or "AQ" or "AG" or "AR" or "AM" or "AW" or "AU" or "AT" or "AZ" or "BS" or "BH" or "BD" or "BB" or "BY" or "BE" or "BZ" or "BJ" or "BM" or "BT" or "BO" or "BQ"

It works fine, until I reach character number 798, than over it, it fails with an error message:

Error in the validation rule: Error in CHECK...(something, I am using a non english office (2007) so I cannot really translate it.)

Any idea? I thought it is because of the length, but it should accommodate 2000 something characters. I could not find anything about the maximum number of expression in a validation rule.

I would appreciate any solution.

Thanks

Upvotes: 1

Views: 1370

Answers (1)

Fionnuala
Fionnuala

Reputation: 91326

I suggest you use a table of acceptable codes, set up a relationship and enforce referential integrity. This type of statement is not what validation is for. You can then use the table in comboboxes in forms to allow the user to easily select the codes.

See http://office.microsoft.com/en-ie/access-help/create-edit-or-delete-a-relationship-HA010072597.aspx

This is tested as a validation rule in Access 2010, but it is horrible!

IN("AX","AF","AL","DZ","AS","AD","AO","AI","AQ","AG","AR","AM","AW","AU","AT","AZ","BS","BH","BD","BB","BY","BE","BZ","BJ","BM","BT","BO","BA","BW","BV","BR","IO","BN","BG","BF","BI","KH","CM","CA","CV","KY","CF","TD","CL","CN","CX","CC","CO","KM","CD","CG","CK","CR","CI","HR","CU","CY","CZ","DK","DJ","DM","DO","EC","EG","SV","GQ","ER","EE","ET","FK","FO","FJ","FI","FR","GF","PF","TF","GA","GM","GE","DE","GH","GI","GR","GL","GD","GP","GU","GT","GN","GW","GY","HT","HM","HN","HK","HU","IS","IN","ID","IR","IQ","IE","IL","IT","JM","JP","JO","KZ","KE","KI","KP","KR","KW","KG","LA","LV","LB","LS","LR","LY","LI","LT","LU","MO","MK","MG","MW","MY","MV","ML","MT","MH","MQ","MR","MU","YT","MX","FM","MD","MC","MN","MS","MA","MZ","MM","NA","NR","NP","NL","AN","NC","NZ","NI","NE","NG","NU","NF","MP","NO","OM","PK","PW","PS","PA","PG","PY","PE","PH","PN","PL","PT","PR","QA","RE","RO","RU","RW","SH","KN","LC","PM","VC","WS","SM","ST","SA","SN","CS","SC","SL","SG","SK","SI","SB","SO","ZA","GS","ES","LK","SD","SR","SJ","SZ","SE","CH","SY","TW","TJ","TZ","TH","TL","TG","TK","TO","TT","TN","TR","TM","TC","TV","UG","UA","AE","GB","US","UM","UY","UZ","VU","VA","VE","VN","VG","VI","WF","EH","YE","ZM","ZW")

Upvotes: 1

Related Questions