Customer Names field Validation Question

[Joel L.]

I was wondering if there was a way to create a validation rule for a table
field for this situation? I want to be able to validate and make sure that
the Name entered has a minimum "2 text characters" followed by a "comma" a
"space" and then followed by another minimum of "2 text characters".

Example: "Li, George" or "Rogers, Ty"

Not: "L, George" or "Rogers, T" or "(blank)" or "Li, (blank)" or "Rogers,
(blank)"

Any solutions or answers would be much appreciated.

 

[KARL DEWEY]

I think you need to use two or three field and do the validation in the data
entry form. I like to use the following fields --
LName
FName
MI or MName
Suffix JR, SR, II, IV, etc.

 

[Designing-Systems.com]

hi,

Since the number of characters cannot be predicted for names, it is not
possible to create a validation rule in the table. You can however create a
rule in code. You can write code as part of a form or application. This would
work if data is not directly entered in the table.

In the code you would store the name field in a string and then look for the
existence of comma in the string. Then count the characters to the left and
the right of the coma. If count before comma is three (one for space, two for
characters) or count after coma is three you validate the record, otehrwise
raise a flag or reject the record.


To look for existence of the comma use the StrComp Function.Then use the
InStr Function to identify the position of the comma. Then use the MID or
Left or Right functions to get the length of the string to the left or right
of the comma.

Regards,

 

[Tim Ferguson]

I want to be able to validate and make sure that
the Name entered has a minimum "2 text characters" followed by a
"comma" a "space" and then followed by another minimum of "2 text
characters".

Access SQL LIKE does not use a proper RE syntax and in particular there are
no repetition counts, so something like this is probably as close as you
can come. Note it has _not_ been tested:

MyField LIKE "[a-zA-Z][a-zA-Z], *"
AND MID(MyField, 5) NOT LIKE "*[^a-zA-Z]*"

Hope it helps


Tim F

 

[Joel L.]

I really appreciate the help in this forum. Thank you for all the replies.
I have decided to go with the seperate fields for the LAst and First names.

Cheers.