how do i validate a field so that only text can be entered?

[snow]

hi:
I have a field which is first name and it is obvious that only text
should be entered, i tried validation and input mask and i tried to find the
solution but i just can't find me, could you tell me how to do it please.
Thanks.

 

[Pavel Romashkin]

hi:
I have a field which is first name and it is obvious that only text
should be entered, i tried validation and input mask and i tried to find the
solution but i just can't find me, could you tell me how to do it please.
Thanks.

You could use IsNumeric function.
However, perhaps it is good enough that you make sure it is clear that
the field is designated for the Name? Maybe the users will enter their
names instead of numbers, then?

Cheers,
Pavel

 

[John Vinson]

hi:
I have a field which is first name and it is obvious that only text
should be entered, i tried validation and input mask and i tried to find the
solution but i just can't find me, could you tell me how to do it please.
Thanks.

So people are entering R2D2 and C3PO?? Isn't this just a user training
issue?

You can use a Validation Rule such as

NOT LIKE "*[0-9_!@#$%^&*()]*" to exclude digits and that set of
special characters.


John W. Vinson[MVP]

 

[Brendan Reynolds]

You can use a Validation Rule such as

NOT LIKE "*[0-9_!@#$%^&*()]*" to exclude digits and that set of
special characters.

In light of the fact Jet text columns are unicode, it would be
impractical to *exclude* characters. Better perhaps to test for valid
characters only e.g.

CREATE TABLE Test (
data_col VARCHAR(10) NOT NULL,
CHECK(data_col & Space$(10 - Len(data_col))
LIKE '[A-Z ][A-Z ][A-Z ][A-Z ][A-Z ][A-Z ][A-Z ][A-Z ][A-Z ][A-Z
]')
);

This will disallow perfectly valid characters such as à, á, â, ã, ä, å, etc.

There is, after all, a *reason* why Jet uses Unicode.

 

[Brendan Reynolds]

You can use a Validation Rule such as

NOT LIKE "*[0-9_!@#$%^&*()]*" to exclude digits and that set of
special characters.

In light of the fact Jet text columns are unicode, it would be
impractical to *exclude* characters. Better perhaps to test for valid
characters only e.g.

CREATE TABLE Test (
data_col VARCHAR(10) NOT NULL,
CHECK(data_col & Space$(10 - Len(data_col))
LIKE '[A-Z ][A-Z ][A-Z ][A-Z ][A-Z ][A-Z ][A-Z ][A-Z ][A-Z ][A-Z
]')
);

This will disallow perfectly valid characters such as à, á, â, ã, ä, å,
etc.

.... and any entry that does not contain exactly 10 characters ...

 

[Brendan Reynolds]

John Vinson wrote:
You can use a Validation Rule such as

NOT LIKE "*[0-9_!@#$%^&*()]*" to exclude digits and that set of
special characters.

In light of the fact Jet text columns are unicode, it would be
impractical to *exclude* characters. Better perhaps to test for valid
characters only e.g.

CREATE TABLE Test (
data_col VARCHAR(10) NOT NULL,
CHECK(data_col & Space$(10 - Len(data_col))
LIKE '[A-Z ][A-Z ][A-Z ][A-Z ][A-Z ][A-Z ][A-Z ][A-Z ][A-Z ][A-Z
]')
);

This will disallow perfectly valid characters such as à, á, â, ã, ä, å,
etc.

... and any entry that does not contain exactly 10 characters ...

Forgot to post the code I used to test ...

Public Sub TestIt()

Dim strSQL As String
strSQL = "CREATE TABLE Test ( data_col VARCHAR(10) NOT NULL, " & _
"CHECK(data_col & Space$(10 - Len(data_col)) LIKE " & _
"'[A-Z ][A-Z ][A-Z ][A-Z ][A-Z ][A-Z ][A-Z ][A-Z ][A-Z ][A-Z]'));"
CurrentProject.Connection.Execute strSQL

End Sub

 

[Brendan Reynolds]

The original poster stated that it was a 'first name' field, Jamie.
Therefore in my opinion it is not a question of what characters are or are
not allowed, but of what characters can or can not appear in a person's
given name. If the person in question is someone important to your
organisation (like a customer) do you want to be the one to explain that
his/her name is 'not allowed'? Hopefully, the days when we tolerated
applications that could not correctly record our names are past, or at least
passing, along with the technical limitations that formerly excused the
practise.

You do raise some interesting points, however, and you are absolutely
correct about the missing trailing space.

--
Brendan Reynolds (MVP)

This will disallow perfectly valid characters such as à, á, â, ã, ä, å, etc.

There is, after all, a *reason* why Jet uses Unicode.

Those characters are ASCII, not Unicode! AFAIK the reason Jet datatypes
were converted to Unicode is to support other languages e.g. Chinese
characters spring to mind, here.

... and any entry that does not contain exactly 10 characters ...
Public Sub TestIt()

Dim strSQL As String
strSQL = "CREATE TABLE Test ( data_col VARCHAR(10) NOT NULL, " & _
"CHECK(data_col & Space$(10 - Len(data_col)) LIKE " & _
"'[A-Z ][A-Z ][A-Z ][A-Z ][A-Z ][A-Z ][A-Z ][A-Z ][A-Z ][A-Z]'));"
CurrentProject.Connection.Execute strSQL

End Sub

Thanks for posting your code because I spotted that the trailing space
is missing from the last pattern matching character i.e. you have [A-Z]
rather than [A-Z ]. The word wrap is to blame here, no doubt.

And who said those accented etc characters were allowed, anyhow? My
rule certainly doesn't <g>. I take your point, though, and I can
incorporate more characters if desired e.g. (beware of the wrap):

CREATE TABLE Test (
data_col VARCHAR(10) NOT NULL,
CHECK(data_col & Space$(10 - Len(data_col)) LIKE
'[A-ZÀ-ÿ ][A-ZÀ-ÿ ][A-ZÀ-ÿ ][A-ZÀ-ÿ ][A-ZÀ-ÿ ][A-ZÀ-ÿ
][A-ZÀ-ÿ ][A-ZÀ-ÿ ][A-ZÀ-ÿ ][A-ZÀ-ÿ ]')
);

This could get quite lengthy, of course, but I still think it's easier
to state what is allowable rather than try to exclude all those Chinese
characters, Japanese characters, etc.

Jamie.

--

 

[Brendan Reynolds]

Many of our customers are Irish schools and colleges. These customers will
not be impressed with a database that can not accept á, é, í, ó, ú and their
upper-case versions. None of these characters are directly available on a
UK/Irish keyboard, but all can be entered using Ctrl+Alt+letter.

True, the ability to accept data in, say, Cyrillic, probably wouldn't
impress these customers very much, despite the presence of many people of
Eastern European origin in Ireland in recent decades. But I have not noticed
any great demand to prevent anyone from using Cyrillic, either.