Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2010
    Posts
    2

    Unanswered: Keep leading zeros while combining two query fields

    I have an autonumber field to create a unique task ID. I also formatted it as "0000" so it contains leading zeros "0001, 0002, 0003 etc". I am trying to combine this in a query with another text field like "ABC" I want the results to look something like "ABC-0001". Currently it will combine as "ABC-1"

    Any ideas?

  2. #2
    Join Date
    Aug 2009
    Location
    Up Nort' Wi
    Posts
    140
    Ok, second time's the charm:

    You can use the format function like so: [field1] & '-' & Format([field2],"0000") within the query builder or SQL for your query and it will maintain the leading 0's.

    i.e.
    Code:
    SELECT Field1, [field1] & '-' & Format([field2],"0000") AS Keeper
    FROM yourTable;
    Sam, hth.
    Last edited by SCrandall; 07-30-10 at 16:51.
    Good, fast, cheap...Pick 2.

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Another possibility is:

    Right("0000" & AutoNumberField, 4)

    or

    Right(String(4,"0") & AutoNumberField, 4)

    The String function is useful if you need to pad a field to more than 10 characters.

  4. #4
    Join Date
    Jul 2010
    Posts
    2

    Keep Leading Zeros

    Thanks guys, it works!!!! Seems so simple now that its working!

  5. #5
    Join Date
    Apr 2013
    Posts
    24
    Quote Originally Posted by DCKunkle View Post
    Another possibility is:

    Right("0000" & AutoNumberField, 4)

    or

    Right(String(4,"0") & AutoNumberField, 4)

    The String function is useful if you need to pad a field to more than 10 characters.
    I had near enough exactly the same problem as the OP and this String suggestion worked brilliantly thanks DC

    Gav

Posting Permissions

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