Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2010
    Posts
    12

    Unanswered: Keeping Leading Zeros in a String?

    I have 3 fields (ClientName, SubClientName, ItemAutoID) which I've combined to make an item number. It's a combination of the first three characters of the ClientName and SubClientName and the ItemAutoID. I've formatted the ItemAutoID so that when it is in the table alone it is 3 characters, but when I put it in the string it takes off the leading zeros. How can I change this? I'd like the finished string to be something like ABC-ABC-123, right now it's ABC-ABC-1. Thanks!

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    "formatting" is the keyword here. Have a look at the Format() string/text function.

    What happens when you get up to 999? How about if 122 gets deleted? Would that be an issue?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Aug 2010
    Posts
    12
    Well you bring up an excellent point. I was thinking this would be a good workaround, but perhaps I'm wrong. The idea is that each item has a Client and SubClient, as I said before the ItemID is a string that has ClientName-SubClientName-###. So if I have client ABC and subclient DEF, ideally I'd like to have items with the same client and subclients have sequential numerical parts, so ABC-DEF-001, ABC-DEF-002, etc. I was thinking that there wouldn't be a way for a query to do this so using the ItemAutoID as the numerical part was the workaround. If there's a better way to do this I'd be thrilled to know! Thanks!

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    auto id is deffo inappropriate for this, especially if you want to prefix things

    however
    bear in mind there is seperation between how you store data and how its represented on the scrteen or paper

    I'd keep everything in its separate columns and store a part no. define your primary key as a composite.
    when displaying always use the format (eg format(mynumericcolumn,"000")

    when doing data capture if you wan tto accept the 11 alphanumerics, but split them into there constituents parts with a bit of VB (Split function comes to mind)
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Aug 2010
    Posts
    12
    Yeah I was thinking that having an autoID number that the EU never sees but that works for relationships, etc. would be good. But is there a way for the db to look at the first 3 alpha characters and assign a number at the end?

Posting Permissions

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