Postcode Formatting

If they are all complete postcodes but some have the spaces missing, you could try:

Expr1: Replace(Left([postcode],Len([postcode])-3) & " " & Right([postcode],3),"  "," ")

where do I put that in the query?

and I need to capitalize aswell

Expr1: UCase(Replace(Left([postcode],Len([postcode])-3) & " " & Right([postcode],3),"  "," "))

Add it as a field in the Query design view.  "Expr1" is just a name which you can replace with something more meaningful, and you need to use your own fieldname for postcode.

See screenshot.



Alternatively if you are doing the query in SQL view: use something like this (with your own table and field names, of course).

SELECT Staff.Postcode, UCase(Replace(Left([postcode],Len([postcode])-3) & " " & Right([postcode],3),"  "," ")) AS FixedPostcode   FROM Staff;

query01.PNG

presumably this is an update query?

Depends what you want to do with it.  

I would probably do it as a select query because if it finds any incomplete postcodes, and you run it as an update query, it wiill screw them up by putting spaces into them in the wrong place, and I'd prefer to keep my original data unaltered.  

But if you want it all fixed permanently by using UPDATE, maybe create a second "old_postcode" field to store the original data just in case you want to go back to it.  You could also use this to help verify what has been amended ( compare the new and old postcode fields and select the records where they are different)

Trapping the correct format on entry is quite hard - there are a lot of valid formats ; the only consistent features are the first letter, and the last group of 1 digit and 2 letters.

A9 9AA
A99 9AA
A9A 9AA
AA9 9AA
AA99 9AA
AA9A 9AA

I've found that trying to enforce a format here causes more problems than it solves.  (e.g. trapping all the cases above, and how to deal with incomplete postcodes)

See http://en.wikipedia.org/wiki/UK_postcodes  for more info.

this is hardly a solution then.....

Use Format:

PostalCodeClean: Format([PostalCode], ">@@@ @@@")

Or in SQL view:

SELECT
  *,
  Format([PostalCode], ">@@@ @@@") AS PostalCodeClean
FROM
  tblYourTable

/gustav

I found this function a long time ago!

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 format",vbInformation
'               End If
'
' Notes:    This routine disregards leading and trailing spaces but there must only be one space between outcodes and incodes
'
'               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.
'
' Colin Byrne (100551,2730@Compuserve.com)
'
    Dim sOutCode As String
    Dim sInCode As String
 
    Dim bValid As Integer
    Dim iSpace As Integer
 
 
    On Error GoTo HandleErr
 
    ' 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 Function
    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
 
 
ExitHere:
    Exit Function
 
    ' Error handling block added by VBA Code Commenter and Error Handler Add-In. DO NOT EDIT this block of code.
    ' Automatic error handler last updated at 18 August 2005 14:31:25
HandleErr:
    Select Case Err.Number
    Case Else
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "modFunctions.IsValidUKPostcode"
    End Select
    Resume ExitHere
    ' End Error handling block.
End Function
 
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
 
 
    On Error GoTo HandleErr
 
    ' 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
 
 
ExitHere:
    Exit Function
 
    ' Error handling block added by VBA Code Commenter and Error Handler Add-In. DO NOT EDIT this block of code.
    ' Automatic error handler last updated at 18 August 2005 14:31:25
HandleErr:
    Select Case Err.Number
    Case Else
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "modFunctions.MatchPattern"
    End Select
    Resume ExitHere
    ' End Error handling block.
End Function

Select allOpen in new window

where do I put this code?

You need to remove existing spaces first:

PostalCodeClean: Format(Replace([PostalCode], " ", ""), ">@@@ @@@")

Or in SQL view:

SELECT
  *,
  Format(Replace([PostalCode], " ", ""), ">@@@ @@@") AS PostalCodeClean
FROM
  tblYourTable

This will handle all format mentioned here #a22983188, with or without embedded spaces.

/gustav