By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
 435,407 Members | 2,842 Online
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,407 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
4 Replies

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

 Expert 100+ P: 1,430 One way is to check the Ascii value of each letter / number Air Code Expand|Select|Wrap|Line Numbers 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 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

 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 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 Feb 13 '17 #5

### Post your reply

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