Postcode Validation (1 Viewer)

[CriticalSock]

Hi,

I've tried to steal an SQL Postcode validator (This is for UK postcodes) and add it to an AfterUpdate event on a field in MS Access. It doesn't work, obviously, but can you help me with where I've gone wrong? The VBA code is:


Private Sub Postal_Code_AfterUpdate()
Dim CHECK_Code As Integer
Dim LResponse As Integer
If InStr("[A-Z][0-9] [0-9][A-Z][A-Z]", Postal_Code) = 0 Then
CHECK_Code = 0
ElseIf InStr("[A-Z][0-9][0-9] [0-9][A-Z][A-Z]", Me.Postal_Code) = 0 Then
CHECK_Code = 0
ElseIf InStr("[A-Z][A-Z][0-9] [0-9][A-Z][A-Z]", Me.Postal_Code) = 0 Then
CHECK_Code = 0
ElseIf InStr("[A-Z][A-Z][0-9][0-9] [0-9][A-Z][A-Z]", Me.Postal_Code) = 0 Then
CHECK_Code = 0
ElseIf InStr("[A-Z][0-9][A-Z] [0-9][A-Z][A-Z]", Me.Postal_Code) = 0 Then
CHECK_Code = 0
ElseIf InStr("[A-Z][A-Z][0-9][A-Z] [0-9][A-Z][A-Z]", Me.Postal_Code) = 0 Then
CHECK_Code = 0
Else: CHECK_Code = 1
End If
If CHECK_Code = 0 Then
LResponse = MsgBox("Postcode Seems Invalid.", vbYesNo, "Continue")
Else
End If

End Sub



Also, how would I put the Check_Code value into the Message box to see what it's bringing back?

 

[Minty]

As is usual with these fori, "It doesn't work" actually tells us nothing about your problem.
Do you get an error code or does it highlight a piece of code that it can't interpret?

I'll guess that the missing Me. highlighted in red is possibly a starting point

Private Sub Postal_Code_AfterUpdate()
Dim CHECK_Code As Integer
Dim LResponse As Integer
If InStr("[A-Z][0-9] [0-9][A-Z][A-Z]", [COLOR="Red"]Me.[/COLOR]Postal_Code) = 0 Then
CHECK_Code = 0
ElseIf InStr("[A-Z][0-9][0-9] [0-9][A-Z][A-Z]", Me.Postal_Code) = 0 Then
....

 

[winshent]

Why not download the postcode data and then validate against real data..

 

[vbaInet]

@winshent, the data changes too often and it's not always readily available.

@CriticalSock, did you download it from a reliable source? I don't know all the variations there are, but it should be easy enough to check. You should be able to find the different postcode formats. The examples I've seen use regular expressions.

But most importantly, your example should be using the LIKE operator and not Instr().

 

[gemma-the-husky]

even if it does work, a validator needs to be in the BEFOREupdate event, the idea being that you validate the entry, and cancel it if the validation fails

also - is this supposed to test a pattern match?

If InStr("[A-Z][0-9] [0-9][A-Z][A-Z]", Postal_Code) = 0 Then

it looks to me like it just tests the string, and will therefore always return 0,

 

[vbaInet]

also - is this supposed to test a pattern match?

If InStr("[A-Z][0-9] [0-9][A-Z][A-Z]", Postal_Code) = 0 Then

it looks to me like it just tests the string, and will therefore always return 0,

But most importantly, your example should be using the LIKE operator and not Instr().

If Me.Postal_Code LIKE "[A-Z][0-9] [0-9][A-Z][A-Z]" Then

 

[CriticalSock]

Thanks for the replies, I'm getting my data from ukpostcodes dot org

I intend on getting helpdesk to update the postcode data from here once a month or so.

The inputted data isn't saved until the whole form is entered so it doesn't matter if the check is done before or after update?

Like instead of Instr looks like the badger, thanks!

 

[CriticalSock]

Ok, I've got past my spelling mistakes and have replaced Instr with Like but I think that Like is not pattern matching the characters. For example, the first clause:

If Me.Postal_Code Like "[A-Z][0-9][0-9][A-Z][A-Z]" Then
CHECK_Code = 0

Should return zero if the first character of Postal_Code is a letter, the second and third character are numbers and the fourth and fifth characters are letters. I think Like is looking at the whole string as one thing?



Private Sub Postal_Code_AfterUpdate()
Dim CHECK_Code As Integer
Dim LResponse As Integer

CHECK_Code = 1

If Me.Postal_Code Like "[A-Z][0-9][0-9][A-Z][A-Z]" Then
CHECK_Code = 0
ElseIf Me.Postal_Code Like "[A-Z][0-9][0-9][0-9][A-Z][A-Z]" Then
CHECK_Code = 0
ElseIf Me.Postal_Code Like "[A-Z][A-Z][0-9][0-9][A-Z][A-Z]" Then
CHECK_Code = 0
ElseIf Me.Postal_Code Like "[A-Z][A-Z][0-9][0-9] [0-9][A-Z][A-Z]" Then
CHECK_Code = 0
ElseIf Me.Postal_Code Like "[A-Z][0-9][A-Z][0-9][A-Z][A-Z]" Then
CHECK_Code = 0
ElseIf Me.Postal_Code Like "[A-Z][A-Z][0-9][A-Z][0-9][A-Z][A-Z]" Then
CHECK_Code = 0
End If

If CHECK_Code = 0 Then
LResponse = MsgBox("Postcode Seems Invalid.", vbYesNo, "Continue")
End If

End Sub

 

[jdraw]

You may want to investigate using "Regular Expressions" (Regex).
There is a link here -validate UK PostalCodes.