Validation 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.

--
Rick B



"BBoller" wrote in message
...
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!

BBoller wrote:
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

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





"Rick B" Anonymous wrote in message
...
I think you'd use something like...

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



I have not tested that though.

--
Rick B



"BBoller" wrote in message
...
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!

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

"BBoller" wrote in message
...
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 wrote:
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

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

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!

" wrote:


Jeff Boyce wrote:
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

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!

--

Ken Snell
MS ACCESS MVP

wrote in message
ups.com...

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

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