Validating a Surname

[Sinclair]

*UPDATED*: NEW PEOBLEM (Validating a Surname)

I need to validate a surname field which obviously can only consist of letters and occasionally spaces and/or hyphens. (i.e. van Driel or Johnson-Crooks)

I managed to create a validation rule which would allow me to have space in the surname field:

Is Null Or Not Like "*[!((a-z) or (\ ))]*"

but when I tried to allow hyphens into the field - I used these codes - but none of them worked:

Is Null Or Not Like "*[!((a-z) or (\ ) or (-))]*"

Is Null Or Not Like "*[!(a-z)]*" Or Not Like (" ") Or Not Like ("-")

Is Null Or Not Like "*[!(a-z)]*" Or Not Like "*[!(\ )]*" Or Not Like "*[!(\-)]*"

Is Null Or Not Like "*[!(a-z)]*" Or Like "*[!(\ )]*" Or Like "*[!(\-)]*"

Is Null Or Not Like "*[!(a-z)]*" Or Not Like "*[(\ )]*" Or Not Like "*[(\-)]*"

Is Null or Not Like "*[!(a-z)]*" or (in ("-","\ "))

Is Null Or Not Like "*[!(a-z)]*" And (Not In ("*[0-9]*"))

Is Null Or Not Like "*[!(a-z)]*" And (Not In ("*[0-9]*")) And (In ("-"," "))

Is Null Or Not Like "*[!(a-z)]*"  Or Like (\ ) Or (-)

Please can smeone help me.

James

 

[odin1701]

Try using Chr(45) for the -

 

[Sinclair]

I tried this code:

Is Null OR Not Like "*[!((a-z) or ("*(Chr(32),Chr(45))*"))]*"

but i got a popup saying:

Reserved error (-3100); there is no message for this error.

so i tried each of these codes seperately:

Is Null OR Not Like "*[!((a-z) or (Chr(32),Chr(45))]*"

Is Null Or Not Like "*[!((a-z) or (in((Chr(32),Chr(45))))]*"

Is Null Or Not Like "*[!((a-z)]*" OR Is Null Or Not Like "*[!((a-z) or (\ ))]*" OR Is Null Or Not Like "*[!(a-z)]*" And "-" And Is Null Or Not Like "*[!(a-z)]*"

Not Like "*[!((a-z)]*" OR Not Like "*[!((a-z) or (\ ))]*" OR Not Like "*[!(a-z)]*" And "-" And Not Like "*[!(a-z)]*"

but when i used a hyphen, i got the invalid popup every times.

(the good news is, it doesn't allow numbers anymore )


However, if I replace the 'And's with '&'s in the final code from the above 4:

Change from:
Not Like "*[!((a-z)]*" OR Not Like "*[!((a-z) or (\ ))]*" OR Not Like "*[!(a-z)]*" And "-" And Not Like "*[!(a-z)]*"

to

Not Like "*[!((a-z)]*" OR Not Like "*[!((a-z) or (\ ))]*" OR Not Like "*[!(a-z)]*" & "-" & Not Like "*[!(a-z)]*"

then it allows numbers again!!

Is there anyway to do this??

 

[DCrake]

Simple Software Solutions

If you are doing this on the after update property why not try this on the key press event and use a select case on the invalid characters.