Validation rule for 1 capital letter?

I like this one....

--

Ken Snell
MS ACCESS MVP

wrote in message
ups.com...

Ken Snell [MVP] wrote:
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'))

On 19 Aug 2005 06:40:39 -0700, wrote:


Ken Snell [MVP] wrote:
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'))

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.

John Nurick wrote:
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?