Validation Rule

[Eranka]

Hi



I am trying to validate a text box in a form and i tried below code but its not working.

Len([Text4])>=8 And Not Like "*[!0-9a-z@$%!#&*]*"

i want the user to include

1.lower case or upper case letter.
2.Numbers
3.Special Characters
4.Not less than 8

Hope someone could help me out to solve my problem.
Thanks in Advance

[isladogs]

Can you just clarify I've understood the requirements correctly, including the list of allowed characters
So for example is * allowed? What about £ or [ or ]



So for example '£' can't be used;
Also that 'AAAAAAAAAA' is no good nor is 'zzzzzzzzzzz' nor '123456789' nor '$!!!asdf1'
but 'Ag23$b3&' would be OK

BTW Here's a useful reference: https://support.office.com/en-ie/art...rule_reference

[Eranka]

Hi Ridders

yes thats the i want user to enter. Can you show me the code that used or can you just clarify whether the code below is correct.

Len([Text4])>=8 And Not Like "*[!0-9a-z@$%!#&*]*"

[isladogs]

I added an EDIT line while you were typing your answer
Can you clarify please

No its not correct as it stands as that would allow any string of 8 or more characters as long as in the allowed list
e.g. AAAAAAAAA would pass the test but AAAA£AAAA wouldn't
I don't understand why you've used Not Like

I've tried all of these but none worked properly:

Len([Text4])>=8 And Not Like "*[!0-9a-zA-Z@$%!#&*]*"

Len([Text4])>=8 And Like "*[!0-9a-zA-Z@$%!#&*]*"

Len([Text4])>=8 And (Like "0-9") And (Like "a-z") And (Like "A-Z") And (Like "!@$%#&*")

Len([Text4])>=8 And Like "[A-Z]*" And Like "[a-z]*" And Like "[0-9]*" And Like "[!@$%!#&*]*"

I thought the last one would work but I was wrong

I'm busy for the next few hours but can look later if nobody else provides the answer first.
However, its a fairly typical password validation rule so I expect googling will find an answer.
If so, please post it

Here's the easy bit:
Validation Text:

You must enter at least 8 characters including:
1. At least two letters - both upper and lower case required
2. At least one number
3. At least one special character ! @ $ % ! # & *

[Eranka]

Hi Ridders

Len([Text4])>=8 And Like "*[0-9]*" And Like "*[a-z]*" And Like "*[@=.^_$%!#&'`{|}*?~/-]*"

The above code works for me. I ignored user using Upper case letters.

[isladogs]

Hi Ridders

Len([Text4])>=8 And Like "*[0-9]*" And Like "*[a-z]*" And Like "*[@=.^_$%!#&'`{|}*?~/-]*"

The above code works for me. I ignored user using Upper case letters.

I had already tried that but it let through values like aaaa12£! even though £ not an allowed character

EDIT: Sorry - now working!
If you need to include upper & lower case characters you need to do a binary compare
Something like:

If InStr(1, "abcdefghijklmnopqrstuvwxyz", Mid(varPassword, intChar, 1), vbBinaryCompare) > 0 Then

[isladogs]

In case you (or anyone else) are interested, I've finished the code that tests ALL the following conditions:

AT LEAST 8 characters including:
1. At least two letters - BOTH upper and lower case required
2. At least one number
3. At least one special character from this list: ! @ $ % # & * (modify as required)

Place this function in a standard module

Public Function ValidatePwd(varPassword As Variant) As Boolean

Dim blnValid As Boolean
Dim blnValidCriteria As Boolean
Dim intChar As Integer

blnValid = False
blnValid = Len(varPassword) >= 8

If blnValid Then
    blnValidCriteria = False
    For intChar = 1 To Len(varPassword)
        If InStr(1, "ABCDEFGHIJKLMNOPQRSTUVWXYZ", Mid(varPassword, intChar, 1), vbBinaryCompare) > 0 Then
            blnValidCriteria = True
            Exit For
        End If
    Next
    blnValid = blnValidCriteria
Else
    GoTo EndRoutine
End If

If blnValid Then
    blnValidCriteria = False
    For intChar = 1 To Len(varPassword)
        If InStr(1, "abcdefghijklmnopqrstuvwxyz", Mid(varPassword, intChar, 1), vbBinaryCompare) > 0 Then
            blnValidCriteria = True
            Exit For
        End If
    Next
    blnValid = blnValidCriteria
Else
    GoTo EndRoutine
End If

If blnValid Then
    blnValidCriteria = False
    For intChar = 1 To Len(varPassword)
        If InStr(1, "0123456789", Mid(varPassword, intChar, 1), vbBinaryCompare) > 0 Then
            blnValidCriteria = True
            Exit For
        End If
    Next
    blnValid = blnValidCriteria
Else
    GoTo EndRoutine
End If

If blnValid Then
    blnValidCriteria = False
    For intChar = 1 To Len(varPassword)
        If InStr(1, "!@$%#&", Mid(varPassword, intChar, 1), vbBinaryCompare) > 0 Then
            blnValidCriteria = True
            Exit For
        End If
    Next
    blnValid = blnValidCriteria
Else
    GoTo EndRoutine
End If


ValidatePwd = blnValid

EndRoutine:
'Debug.Print blnValid

If blnValid = True Then
    MsgBox "This is a valid password"
Else
    MsgBox "You must enter AT LEAST 8 characters including:" & vbCrLf & _
        "1. At least two letters - both upper and lower case required" & vbCrLf & _
        "2. At least one number" & vbCrLf & _
        "3. At least one special character ! @ $ % # & *"
End If

End Function

In your form, place this code in the Before_Update event of your textbox e.g. Text4

Private Sub Text4_BeforeUpdate(Cancel As Integer)
    ValidatePwd Text4
End Sub

The advantage of this method is it can be called from anywhere - no validation code needed
HTH

[Eranka]

Hi

Sorry mate. Thanks for the above. It works greats. appreciate alot mate.