Validation rule for 1 capital letter?

[BBoller]

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!

 

[Rick B]

I think you'd use something like...

Left([somecontrol],1) = UCase(Left([Somecontrol],1))



I have not tested that though.

 

[peregenem]

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

 

[Ken Snell [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
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

--

Ken Snell
<MS ACCESS MVP>

I think you'd use something like...

Left([somecontrol],1) = UCase(Left([Somecontrol],1))



I have not tested that though.

--
Rick 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
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!

 

[Jeff Boyce]

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>

 

[peregenem]

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

 

[peregenem]

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)
);

 

[BBoller]

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!

 

[Ken Snell [MVP]]

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!

 

[peregenem]

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'))

 

[Ken Snell [MVP]]

I like this one....

 

[John Nurick]

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?

 

[peregenem]

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?