Data validation 2

[jkupov]

How would I go about validating data in a field? For example, I have a field which stores an alpha-numeric value entered by a technician that must match a given format (10 digits long, letters & numbers in certain places within the field, etc.). So an example would be x56kBy67G0. This data (unfortunately)is manually entered by thetechnician without any kind of data validation taking polace in the software- so the technician can eaily (and often does) enter the incorrect number. characters # 1,4, &6 must be lowercase numbers, 5&9 must be Uppercase letters, and charaters 2,3,7,8,&10 must be numbers.

I need to be able to both identify the incorrect entries and determine the percentage of correctly entered values by technician.

Let me know if I'm not being clear. I'm three days into a coffee fast so my mind is numb.

 

[jkupov]

Good Lord please tell me I can edit my poor typing in this thread! I don't see an edit button.

 

[Madawc]

You can test for numbers using Numerictext({your.field}).

No way to change anything you have posted.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[Charliy]

len({field}) = 10
and
left({field,1) = lowercase(left({field},1})
and
numerictext(mid({field},2,2))

and so on

 

[jkupov]

Thank you both (Madawc & Charliy)- I had completely forgotten about numerictext!

 

[jkupov]

one more quick question, I'm getting conflicting reports about whether the text is case sensitive. so in your example above, if chacter 1 must be a letter but case is not important, how do you validate that it is indeed a letter(case agnostic) and not a number?

 

[jkupov]

I tried:

len({field}) = 10
and
not numerictext(left({field},1})
and
numerictext(mid({field},2,2))


But it resturned an error about having too many arguments. However, when I tried again it seems to have worked. So i think I've got it. Just need to validate that it is working as desired now.

 

[Charliy]

There is no "IsAlpha" or anything like that, but if you use
{field} in 'A' to 'Z'
it is not case sensitive

 

[Charliy]

While it seemed like {x} = lowercase({x}) would be an obvious, easy solution - it turns out that it doesn't work. It gives a True wether the letter is Upper or Lower (same for Uppercase). I found a good alternative by using the ascw function.


Upper Case: ascw({x}) in 65 to 90
Lower Case: ascw({x}) in 97 to 122

I may be reinventing the wheel (my favorite hobby), but here is a UDF that wil tell you what type a character is.

Function (STRINGVAR RAW)
// FUNCTION CHARTYPE
// INPUT A single character
// Output What is that character: NUMBER, UPPER, LOWER, SPACE, OTHER
IF NumericText (RAW) THEN 'NUMBER'
ELSE
if ASCW(RAW) in 97 to 122 then 'LOWER'
ELSE
IF RAW IN 'A' TO 'Z' THEN 'UPPER'
ELSE
IF RAW = ' ' THEN 'SPACE'
else 'OTHER'