By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,476 Members | 1,390 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,476 IT Pros & Developers. It's quick & easy.

Check that field meets set criteria

P: 2
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
Feb 10 '17 #1

✓ answered by PhilOfWalton

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

Then modify the code to
Expand|Select|Wrap|Line Numbers
  1. Function CheckLicence(LicenceNo as string) as Boolean
  2.     Dim i as Integer
  3.  
  4.     For i = 1 to 5
  5.         If Asc(Mid(LicenceNo, i,1)) < 65 OR Asc(Mid(LicenceNo, i,1)) > 123 then
  6.             Exit Function
  7.        End If
  8.     Next i
  9.  
  10.  For i = 6 to 11
  11.         If Asc(Mid(LicenceNo, i,1)) < 48 OR Asc(Mid(LicenceNo, i,1)) > 57 then
  12.             Exit Function
  13.        End If
  14.     Next i
  15.  
  16.     For i = 12 to 13
  17.         If Asc(Mid(LicenceNo, i,1)) < 65 OR Asc(Mid(LicenceNo, i,1)) > 123 then
  18.             Exit Function
  19.        End If
  20.     Next i
  21.  
  22. CheckLicence = True
  23.  
  24. End Function
  25.  
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

Share this Question
Share on Google+
4 Replies


Frinavale
Expert Mod 5K+
P: 9,731
Have you considered using Regular Expressions?
Feb 10 '17 #2

PhilOfWalton
Expert 100+
P: 1,430
One way is to check the Ascii value of each letter / number

Air Code
Expand|Select|Wrap|Line Numbers
  1. Dub CheckLicence(LicenceNo as string)
  2.     Dim i as Integer
  3.  
  4.     For i = 1 to 5
  5.         If Asc(Mid(LicenceNo, i,1)) < 65 OR Asc(Mid(LicenceNo, i,1)) > 123 then
  6.             Msgbox "Initial letters are wrong!, VBCritical
  7.             Exit Sub
  8.        End If
  9.     Next i
  10.  
  11.  For i = 6 to 11
  12.         If Asc(Mid(LicenceNo, i,1)) < 48 OR Asc(Mid(LicenceNo, i,1)) > 57 then
  13.             Msgbox "Initial numbers are wrong!, VBCritical
  14.             Exit Sub
  15.        End If
  16.     Next i
  17.  
  18.  
  19.     For i = 12 to 13
  20.         If Asc(Mid(LicenceNo, i,1)) < 65 OR Asc(Mid(LicenceNo, i,1)) > 123 then
  21.             Msgbox "Final letters are wrong!, VBCritical
  22.             Exit Sub
  23.        End If
  24.     Next i
  25.  
  26.  
Phil
Feb 10 '17 #3

P: 2
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.
Feb 13 '17 #4

PhilOfWalton
Expert 100+
P: 1,430
OK, you only need a minor modification
Create your query and have a column LicenceOK:CheckLicence(LicenceNo)

Then modify the code to
Expand|Select|Wrap|Line Numbers
  1. Function CheckLicence(LicenceNo as string) as Boolean
  2.     Dim i as Integer
  3.  
  4.     For i = 1 to 5
  5.         If Asc(Mid(LicenceNo, i,1)) < 65 OR Asc(Mid(LicenceNo, i,1)) > 123 then
  6.             Exit Function
  7.        End If
  8.     Next i
  9.  
  10.  For i = 6 to 11
  11.         If Asc(Mid(LicenceNo, i,1)) < 48 OR Asc(Mid(LicenceNo, i,1)) > 57 then
  12.             Exit Function
  13.        End If
  14.     Next i
  15.  
  16.     For i = 12 to 13
  17.         If Asc(Mid(LicenceNo, i,1)) < 65 OR Asc(Mid(LicenceNo, i,1)) > 123 then
  18.             Exit Function
  19.        End If
  20.     Next i
  21.  
  22. CheckLicence = True
  23.  
  24. End Function
  25.  
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
Feb 13 '17 #5

Post your reply

Sign in to post your reply or Sign up for a free account.