Validate text in fields

[Guest]

I need to enter data in Access table that is validated after update.

The format is based on UK post code. The entry can be in format of AA11 1AA
- the first AA however could also be single digit or letter eg A11 or AA11
Example - big cities often only have a sinle letter (London L1), but smaller
places may be say LL
How can I allow the user to enter data, and then ensure that the field is
concatenated after update -

The same also applies to a phone number field - where the field must include
a leading '0'

Any help would be very much appreciated..
Thanks

 

[Arvin Meyer [MVP]]

Try this function by Mike Bunyan to validate in afterupdate
Beware of text wrap

Attribute VB_Name = "UKPostCode"
Option Compare Database 'Use database order for string comparisons

Function IsValidUKPostcode(ByVal sPostcode As String) As Integer
' Function: IsValidUKPostcode
'
' Purpose: Check that a postcode conforms to the Royal Mail formats for UK
postcodes
'
' Params: sPostcode- Postcode string
'
' Returns: True (-1) - Postcode conforms to valid pattern
' False (0) - Postcode has failed pattern matching
'
' Usage: If Not Valid_UKPostcode(Me!PostCode) Then
' MsgBox "Invalid postcode",vbInformation
' End If
'----------------------------AfterUpdate--------
'calls module IsValidUKPostcode after update
'Private Sub PostCode_AfterUpdate()
'If Not IsNull([PostCode]) Then
' If Not IsValidUKPostcode([PostCode]) Then MsgBox "Invalid PostCode
format", vbInformation
'End If
'End Sub
'Option Compare Database
'Option Explicit
'----------------------------AfterUpdate--------
' Notes: This routine disregards leading and trailing spaces
' but there must only be one space between outcodes and incodes
' Capitalisation is not tested. Try using an input mask like >CCCCCCCC;
'
' Valid UK postcode formats
' Outcode Incode Example
' AN NAA B1 6AD
' ANN NAA S31 2BD
' AAN NAA SW5 8SG
' ANA NAA W1A 4DJ
' AANN NAA CB10 2BQ
' AANA NAA EC2A 1HQ
'
' Incode letters AA cannot be one of C,I,K,M,O or V.
' Based on discussion by John Douglas in Personal Computer World Articles
June 1998
' Michael Bunyan ([email protected])
'
Dim sOutCode As String
Dim sInCode As String

Dim bValid As Integer
Dim iSpace As Integer

' Trim leading and trailing spaces
sPostcode = Trim(sPostcode)

iSpace = InStr(sPostcode, " ")

' If there is no space in the string then it is not a full postcode
If iSpace = 0 Then
IsValidUKPostcode = False
Exit Sub
End If

' Split post code into outcode and incodes
sOutCode = Left$(sPostcode, iSpace - 1)
sInCode = Mid$(sPostcode, iSpace + 1)

' Check incode is valid
' ... this will also test that the length is a valid 3 characters long
bValid = MatchPattern(sInCode, "NAA")

If bValid Then
' Test second and third characters for invalid letters
If InStr("CIKMOV", Mid$(sInCode, 2, 1)) > 0 Or InStr("CIKMOV",
Mid$(sInCode, 3, 1)) > 0 Then
bValid = False
End If
End If

If bValid Then
Select Case Len(sOutCode)
Case 0, 1
bValid = False
Case 2
bValid = MatchPattern(sOutCode, "AN")
Case 3
bValid = MatchPattern(sOutCode, "ANN") Or MatchPattern(sOutCode,
"AAN") Or MatchPattern(sOutCode, "ANA")
Case 4
bValid = MatchPattern(sOutCode, "AANN") Or
MatchPattern(sOutCode, "AANA")
End Select
End If

' If bValid is False by the time it gets here
' ...it has failed one of the above tests
IsValidUKPostcode = bValid

End Sub

Function MatchPattern(ByVal sString As String, ByVal sPattern As String) As
Integer


Dim cPattern As String
Dim cString As String

Dim iPosition As Integer
Dim bMatch As Integer

' If the lengths don't match then it fails the test
If Len(sString) <> Len(sPattern) Then
MatchPattern = False
Exit Function
End If

' All strings to uppercase - ByVal ensures callers string is not affected
sString = UCase(sString)
sPattern = UCase(sPattern)

' Assume it matches until proven otherwise
bMatch = True

For iPosition = 1 To Len(sString)

' Take the characters at the current position from both strings
cPattern = Mid$(sPattern, iPosition, 1)
cString = Mid$(sString, iPosition, 1)

' See if the source character conforms to the pattern one
Select Case cPattern
Case "N" ' Numeric
If Not IsNumeric(cString) Then bMatch = False
Case "A" ' Alphabetic
If Not (cString >= "A" And cString <= "Z") Then bMatch = False
End Select

Next iPosition

MatchPattern = bMatch

End Function

 

[Jamie Collins]

(ANSI-92 query mode wildcard characters):

CREATE TABLE UKPostalAddresses (
postcode VARCHAR(8),
CONSTRAINT uk_postcode__pattern
CHECK
(
postcode LIKE '[A-Z][0-9] [0-9][A-Z][A-Z]'
OR postcode LIKE '[A-Z][0-9][0-9] [0-9][A-Z][A-Z]'
OR postcode LIKE '[A-Z][A-Z][0-9] [0-9][A-Z][A-Z]'
OR postcode LIKE '[A-Z][A-Z][0-9][0-9] [0-9][A-Z][A-Z]'
OR postcode LIKE '[A-Z][0-9][A-Z] [0-9][A-Z][A-Z]'
OR postcode LIKE '[A-Z][A-Z][0-9][A-Z] [0-9][A-Z][A-Z]'
),

CONSTRAINT uk_postcode__invalid_chars_pos_1
CHECK (postcode NOT LIKE '[QVX]%'),

CONSTRAINT uk_postcode__invalid_chars_pos_2
CHECK (postcode NOT LIKE '_[IJZ]%'),

CONSTRAINT uk_postcode__valid_chars_pos_3
CHECK
(
1 = IIF(postcode LIKE '[A-Z][0-9][A-Z]%',
IIF(postcode LIKE '[A-Z][0-9][ABCDEFGHJKSTUW]%', 1, 0), 1)
),

CONSTRAINT uk_postcode__valid_chars_pos_4
CHECK
(
1 = IIF(postcode LIKE '[A-Z][A-Z][0-9][A-Z]%',
IIF(postcode LIKE '[A-Z][A-Z][0-9][ABEHMNPRVWXY]%', 1, 0), 1)
),


CONSTRAINT uk_postcode__valid_chars_inward_part
CHECK
(
postcode NOT LIKE '%[CIKMOV]_'
AND postcode NOT LIKE '%[CIKMOV]'
)
);

(ANSI wildcard characters only):

national_number VARCHAR(20) NOT NULL,
CONSTRAINT telephone_contact_detail_national_number__pattern
CHECK (
national_number NOT LIKE '%[!0-9 -]%'
AND national_number LIKE '%[0-9]%'
),
CONSTRAINT telephone_contact_detail_national_number__spaces
CHECK
(
national_number NOT LIKE ' %'
AND national_number NOT LIKE '% '
AND national_number NOT LIKE '% %'
),

CONSTRAINT telephone_contact_detail_national_number__hyphens
CHECK
(
national_number NOT LIKE '-%'
AND national_number NOT LIKE '%-'
AND national_number NOT LIKE '%--%'
),

extension_number VARCHAR(6)
CONSTRAINT telephone_contact_detail_extension_number__pattern
CHECK (
extension_number NOT LIKE '%[!0-9]%'
AND extension_number LIKE '%[0-9]%'
),

country_number VARCHAR(3) DEFAULT '44' NOT NULL
CONSTRAINT telephone_contact_detail_country_number__pattern
CHECK (
country_number NOT LIKE '%[!0-9]%'
AND country_number LIKE '%[0-9]%'
)

For UK only you'd also want a validation rule for

country_number = '44'

Jamie.

--