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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
snow said:
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.
Click to expand...

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
 
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.
Click to expand...

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]
 
John said:
You can use a Validation Rule such as

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

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
]')
);
So people are entering R2D2 and C3PO?? Isn't this just a user training
issue?
Click to expand...

You wouldn't suggest referential integrity was a user training issue,
would you? Same should go for data integrity: if you don't want it in
your database then don't allow it in.

Jamie.

--
 
Jamie Collins said:
John said:
You can use a Validation Rule such as

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

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
]')
);
Click to expand...

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

There is, after all, a *reason* why Jet uses Unicode.
 
Brendan Reynolds said:
Jamie Collins said:
John said:
You can use a Validation Rule such as

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

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
]')
);
Click to expand...

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

.... and any entry that does not contain exactly 10 characters ...
 
Brendan Reynolds said:
Brendan Reynolds said:
Jamie Collins said:
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
]')
);
Click to expand...

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

... and any entry that does not contain exactly 10 characters ...
Click to expand...

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 said:
This will disallow perfectly valid characters such as à, á, â, ã, ä, å, etc.

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

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
Click to expand...

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.

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


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

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

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
Click to expand...

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 said:
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'?
Click to expand...

I think the characters available on the keyboard(s) used by the
organization would be a good starting point. I don't think I need to
explain to Hu Jintao why I don't use Chinese characters in my
'Presidents' database.

Jamie.

--
 
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.
 
Back
Top