 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / August 2005Validation rule for 1 capital letter?
Hi all - apologies if this is a stupid question! I have a colleague who is attempting to set up a Validation Rule so that the first letter in the field must be a capital letter. He wants to use that rather than an input mask so that an error message is received if the capital letter is not entered. I have looked around for something similar and being (admittedly) new to the idea of Validation rules, I'm stuck. Can anyone offer any suggestions? Thank you! I think you'd use something like... Left([somecontrol],1) = UCase(Left([Somecontrol],1)) I have not tested that though.  SignatureRick B > Hi all - apologies if this is a stupid question! I have a colleague who is > attempting to set up a Validation Rule so that the first letter in the field [quoted text clipped - 4 lines] > > Thank you! Rick's suggestion will work in EXCEL VBA, which is case-sensitive when comparing a value in a cell, but not in ACCESS VBA, which is not case-sensitive when comparing a value in a control. Use StrComp function, or do an Asc() comparison. This: If StrComp(Left([somecontrol],1), UCase(Left([Somecontrol],1)), _ vbBinaryCompare) <> 0 Then ' they don't match Else ' they do match End If or this: If Asc(Left([somecontrol],1)) <> Asc(UCase(Left([somecontrol],1))) Then ' they don't match Else ' they do match End If SignatureKen Snell <MS ACCESS MVP> >I think you'd use something like... > [quoted text clipped - 14 lines] >> >> Thank you! > Rick's suggestion will work in EXCEL VBA, which is case-sensitive when > comparing a value in a cell, but not in ACCESS VBA, which is not > case-sensitive when comparing a value in a control. Another thought: the OP could test the ascii value of the first character e.g. CREATE TABLE Test ( last_name VARCHAR(35) NOT NULL, CHECK(ASC(LEFT$(last_name, 1)) BETWEEN 65 AND 90) ); Yep, that would work... I just can never remember the exact ascii number for those letters, and always have to go look them up or run a test in the Immediate Window... laziness sometimes interferes! SignatureKen Snell <MS ACCESS MVP> >> Rick's suggestion will work in EXCEL VBA, which is case-sensitive when >> comparing a value in a cell, but not in ACCESS VBA, which is not [quoted text clipped - 7 lines] > CHECK(ASC(LEFT$(last_name, 1)) BETWEEN 65 AND 90) > ); > I just can never remember the exact ascii number for > those letters, and always have to go look them up or run a test in the > Immediate Window... laziness sometimes interferes! OK this then, which does actually read better: CHECK(ASC(LEFT$(last_name, 1)) BETWEEN ASC('A') AND ASC('Z')) ;) I like this one.... SignatureKen Snell <MS ACCESS MVP> >> I just can never remember the exact ascii number for >> those letters, and always have to go look them up or run a test in the [quoted text clipped - 5 lines] > > ;) >> I just can never remember the exact ascii number for >> those letters, and always have to go look them up or run a test in the [quoted text clipped - 3 lines] > >CHECK(ASC(LEFT$(last_name, 1)) BETWEEN ASC('A') AND ASC('Z')) It would be nice to make it international. For instance in Perl or .NET one can take advantage of Unicode properties and match the string against "^\p{Lu}". Is it possible to do something analogous in Jet SQL ... or any SQL? -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. > It would be nice to make it international. For instance in Perl or .NET > one can take advantage of Unicode properties and match the string > against "^\p{Lu}". Is it possible to do something analogous in Jet SQL > ... or any SQL? For Jet, this isn't possible at the data engine level because Jet doesn't support 'proper' regular expressions; rather, we merely get the simple pattern matching the VBA Like keyword supports. Native SQL Server/MSDE support isn't any better. I can't recall any other SQLs doing much better either. You can write a T-SQL (SQL Server/MSDE) extended stored procedure to use a more advanced regex code library which could then be applied to a CHECK constraint i.e. applied at the data server level rather in a front end application. I don't think you can do the same using VBA in Access because Validation rules get written into the schema as CHECK constraints, but I'm not 100% sure on this. Anyone? > I have a colleague who is > attempting to set up a Validation Rule so that the first letter in the field > must be a capital letter. I don't think you'll have any luck because the check is case insensitive :( Why? If the only purpose of the validation check is to tell the user s/he didn't capitalize the first character, why not just capitalize the first character for him/her, no matter what? Regards Jeff Boyce <Access MVP> > Hi all - apologies if this is a stupid question! I have a colleague who is > attempting to set up a Validation Rule so that the first letter in the [quoted text clipped - 6 lines] > > Thank you! > Why? If the only purpose of the validation check is to tell the user s/he > didn't capitalize the first character, Good point. The answer is, there is a difference between validation in the front end application, which is this case is Access, and a constraint in the database i.e. a CHECK constraint a.k.a. Validation Rule. After Access, Excel the next most popular application used to access data in a Jet database. Putting validation in an Access form will not prevent the same user with the same permissions putting bad data into the same Jet database while connected via Excel. Because the CHECK is applied at the data engine level, it applies equally to *all* front end/middle tier applications, so data integrity is ensured. > why not just capitalize the first > character for him/her, no matter what? Another good point. Best to fix the leak as well as mopping the floor: http://www.dbazine.com/ofinterest/oi-articles/celko25 Thank you all for your help! I had the same question, but he is teaching and wanted to do it this particular way. I'll pass everything along that was suggested. I appreciate it! > > Why? If the only purpose of the validation check is to tell the user s/he > > didn't capitalize the first character, [quoted text clipped - 15 lines] > > http://www.dbazine.com/ofinterest/oi-articles/celko25 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.