Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2008
    Posts
    4

    Unanswered: how can i format a query column by a set # of characters?

    i have been given a spreadsheet with a particular column which i need to query.

    i have transfered the data to an access db and now i want to format that column to have 8 numbers.

    below is the column which i want to format. The format criteria is that it has to start with at least one 0 and the total number of characters must not exceed 8.

    search
    0000000029402
    0000000029855
    0000000030416889
    0000000030524830
    0000000095A495
    0000000095A496
    0000000095A798
    0000000095C049
    000000009000189
    00000000990702
    00000000994831
    00000000919645
    00000000919646
    00000000919647

    how can i do this??
    pleasehelp.

    thanks.

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    If it's a numeric field in Access, use a Format of 00000000

    If it's a text field in Access, use an expression: Right(Fieldname,8)
    Last edited by demar_quiz; 07-26-08 at 07:24.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Id look at
    =format(cint(mystringvar),"00000000")

    the cint() converts the string number into an integer, the right add leading zeros to pad the number to add digits
    it will however have a problem with non numeric values.

    just using right() will chop any numbers with more than 8 character to 8, but any number with less than 8 will not get extra characters added

    so you could do something such as
    =right("00000000" & mystringvar,8), that will always return 8 characters padded with leading 0's. it will handle non numeric values, it will handle nulls
    ..all we do is add 8 0's to the front of the mystringvar and then take the last (rightmost) 8 characters. its not pretty but it works
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Aug 2008
    Posts
    4
    thanks Guys,

    both solutions work.

    cheers

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Mine's prettier ^.^

    Glad you got it sorted
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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