Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jan 2003
    Location
    Grass Valley, California, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Field Formatting (2000)

    I would like to be able to conditionally format a postal zipcode into one of three formats dependent upon the number of characters provided. Specifically, if there are 5 characters (numeric), I'd like to display it as "nnnnn". If it is 6 characters (alphanumeric), I'd like to display it as "aaa aaa". And if it is 9 characters (numeric), I'd like to display it as "nnnnn-nnnn".

    Is there any method to accomplish such a conditional display on a form?

    Thanks, Jim

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Conditional Field Formatting (2000)

    One option, you can use a user-defined function. Example:

    Public Function FormatPostalCode(ByRef strPostalCode As String) As String

    Dim intLen As Integer
    intLen = Len(strPostalCode)

    Select Case intLen
    Case 5
    FormatPostalCode = strPostalCode
    Case 6
    FormatPostalCode = Format$(strPostalCode, "@@@ @@@")
    Case 9
    FormatPostalCode = Format$(strPostalCode, "@@@@@-@@@@")
    Case Else
    ' test only
    MsgBox "Invalid input.", vbExclamation, "INVALID INPUT"
    FormatPostalCode = vbNullString
    End Select

    End Function

    You can also format the field in a query using the Switch function. Example SQL:

    SELECT PostalCodes.PostalCode, FormatPostalCode(Nz([PostalCode])) AS [Postal Code1], Switch(Len(Nz([PostalCode]))=5,[PostalCode],Len(Nz([PostalCode]))=6,Format$([PostalCode],"@@@ @@@"),Len(Nz([PostalCode]))=9,Format$([PostalCode],"@@@@@-@@@@"),Len(Nz([PostalCode]))=0,"") AS [Postal Code2]
    FROM PostalCodes;

    This query uses both the function and a query expression using Switch to get the same results. Of course calculated field used on form will not be updatable; you'd have to use control bound to actual PostalCode field (unformatted). Also note that no attempt is made to validate whether PostalCode is in proper format as far as alphanumeric or numeric-only characters, only the length is tested. If necessary modify function for further validation.

    HTH

  3. #3
    Star Lounger
    Join Date
    Jan 2003
    Location
    Grass Valley, California, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Field Formatting (2000)

    Thanks Mark. I suspected that a user-function was my only option. I was hoping there was some "higher-level function" within Access that might allow me to define a conditional format of this type -- Guess not.

    -- Jim

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •