Check that field meets set criteria

[knee78]

Hi,

I need to check though a huge amount of driving licence numbers and pick out ones that do not confirm to the predefined layout of a driving licence.

For example I might want the first 5 characters to be a letter followed by 6 numbers and then 2 letters. I want to be able to identify anything that does not follow this structure. eg :-

Driving licence Number Correct format ?
Jones220578GG Y
Jones24ddwe96 N

[Frinavale]

Have you considered using Regular Expressions?

[PhilOfWalton]

One way is to check the Ascii value of each letter / number

Air Code

Dub CheckLicence(LicenceNo as string)
    Dim i as Integer
 
    For i = 1 to 5
        If Asc(Mid(LicenceNo, i,1)) < 65 OR Asc(Mid(LicenceNo, i,1)) > 123 then
            Msgbox "Initial letters are wrong!, VBCritical
            Exit Sub
       End If
    Next i
 
 For i = 6 to 11
        If Asc(Mid(LicenceNo, i,1)) < 48 OR Asc(Mid(LicenceNo, i,1)) > 57 then
            Msgbox "Initial numbers are wrong!, VBCritical
            Exit Sub
       End If
    Next i
 
 
    For i = 12 to 13
        If Asc(Mid(LicenceNo, i,1)) < 65 OR Asc(Mid(LicenceNo, i,1)) > 123 then
            Msgbox "Final letters are wrong!, VBCritical
            Exit Sub
       End If
    Next i
 
 

Phil

[knee78]

Hi Sorry I could get this to work. I needed to have a query really. So the field in the query would be the LicenceNo and then a Y/N for whether it conforms to the licence layout.

[PhilOfWalton]

OK, you only need a minor modification
Create your query and have a column LicenceOK:CheckLicence(LicenceNo)

Then modify the code to

Function CheckLicence(LicenceNo as string) as Boolean
    Dim i as Integer
 
    For i = 1 to 5
        If Asc(Mid(LicenceNo, i,1)) < 65 OR Asc(Mid(LicenceNo, i,1)) > 123 then
            Exit Function
       End If
    Next i
 
 For i = 6 to 11
        If Asc(Mid(LicenceNo, i,1)) < 48 OR Asc(Mid(LicenceNo, i,1)) > 57 then
            Exit Function
       End If
    Next i
 
    For i = 12 to 13
        If Asc(Mid(LicenceNo, i,1)) < 65 OR Asc(Mid(LicenceNo, i,1)) > 123 then
            Exit Function
       End If
    Next i
 
CheckLicence = True
 
End Function
 

So what happens is that if any of the tests fail, CheckLicence returns 0 (False). If all the tests pass, we set CheckLicence to True.

Phil