Stopping rubbish date entry on a form?

[TheWennerWoman]

Hello, I have a form which contains about a dozen data entry points.

A lot of them are mandatory and so I have some validation that basically says if the field in question has no data in then generate an error message and return them to where they were.

Except I now see from the database that sits behind the form that the users have been entering full stops or commas to fool the form into thinking it's been populated correctly.

Is there some validation I can apply? I have introduced something along the lines of "if the field in question contains less than five characters then reject" but that won't stop the users entering six full stops.

Many thanks for reading.

 

[mumps]

Do some research into the "IsDate" function.

 

[Snakehips]

Are the potential rubbish entry fields solely requiring dates?

 

[TheWennerWoman]

Are the potential rubbish entry fields solely requiring dates?

No, that's my main issue, they are free text fields (spit).....I've managed to stick validation on the date fields and dropdown boxes on the form are governed by lists....it's these wretched text fields (and users who think it's amusing to put a full stop in when it should be a name of a person for example).

 

[TheWennerWoman]

Do some research into the "IsDate" function.

My apologies - the title should read "data entry" not date entry! I've managed to put validation on the date fields, it's other fields that are causing me grief.

 

[Snakehips]

My apologies - the title should read "data entry" not date entry! I've managed to put validation on the date fields, it's other fields that are causing me grief.

I did wonder that!

Sadly, there is no end to the ways a determined pita can overcome any validation rules if entry is free text.
All I think you can do is try and put an obstacle or two in their way to maybe prevent an obvious violation or two?
I do have a couple of ideas that may help but maybe cannot respond for an hour or two.
Can you maybe describe or give a couple of examples of what would be a valid response?
Eg Maybe the max words expected.
Would you expect response to include punctuation marks other than comma and full stops?

 

[Snakehips]

@TheWennerWoman It's a bit crude but might something like below give a degree of validation?
If called as as a Sub, it needs the entry text passing to it.
Not extensively tested so

Sub Test(Txt As String)
Dim Txt2, F, L As String
Dim LT, AscF, AscS, AscL As Integer

'***need to pass Txt string as per form entry field string  *****

'remove commas and stops and spaces
Txt = Join(Split((Join(Split(Join(Split(Txt, ","), ""), "."), "")), " "), "")

'check is minimum length
LT = Len(Txt)
If Not LT >= 10 Then GoTo BadEntry '<<< edit # to suit?

'convert Txt to uppercase for purpose of validation
Txt = UCase(Txt)

'get first second and last characters of Txt
F = Left(Txt, 1)
L = Right(Txt, 1)
S = Mid(Txt, 2, 1)
'test expecting remaining characters to be random alpha

'check if characters just repeat
Txt2 = Join(Split(Txt, F), "")
If Txt2 = vbNullString Then GoTo BadEntry

'Get Asci for firs,  last and second characters
AscF = Asc(F)
AscL = Asc(L)
AscS = Asc(S)
'check that first letter is only alpha A-Z
If Not (AscF > 64 And AscF < 91) Then GoTo BadEntry

'check that last letter is only alpha A-Z
If Not (AscL > 64 And AscL < 91) Then GoTo BadEntry

 'rough check that characters are not likely not consecutive alphas ???????
 If (AscL = AscF + LT - 1) And (AscF = AscS - 1) Then GoTo BadEntry
 
Exit Sub
BadEntry:
'Do whatever to admonish / request a valid entry
MsgBox "Please stop taking thre proverbial and make a valid entry!"

End Sub

 

[Snakehips]

Maybe for starters, test the principle by changing a cell in test sheet and call Test with the WS change event?

Sub Worksheet_Change(ByVal Target As Range)
If Not Target.Cells.Count = 1 Then Exit Sub

Call Test(Target.Text)
End Sub

 

[TheWennerWoman]

Maybe for starters, test the principle by changing a cell in test sheet and call Test with the WS change event?

Sub Worksheet_Change(ByVal Target As Range)
If Not Target.Cells.Count = 1 Then Exit Sub

Call Test(Target.Text)
End Sub

Really useful, thank you