Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004
    Posts
    10

    Unanswered: Autonumber Alphanumeric

    Hi i am trying to use an alphanumeric autonumber E.g.

    AAA0001

    So i can go through from AAA0001 - AAA9999
    And then AAB0001-AAB9999 etc

    You get the idea....

    How is this doen in access - any ideas please

    Please also note that teh database its being used for will eventually migrate up to MS SQL but not for a while...

    Thanks !!!

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    it isn't done in access!

    a's autonumbers are long integers: you get 2.4 billion of them but they don't contain alphas.

    you can find fanatics on the web who insist that primary keys should never have a "meaning" (i.e. they should not be your invoice number). a's autonumbers fit the bill as small meaningless bits of glue you can use to stick tables together.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you can do it, or get close to it, but you would have to write a function to handle it. Unless you have a 64 bit processor you wouldn't be able to get AAAA0000 as the range that requires is outside the scope of the long datatype on 32 bit machines.

    You would have to do a comparision and move left to right factoring out the ranges for the letters
    eg 'note this is not "real code" just the methodology
    if orderno> (26*26*26*10*10*10*10) then
    newno=chr$(65+(orderno-(26*26*26*10*10*10*10)/26))
    orderno=orderno-(26*26*26*10*10*10*10)/26))
    else
    newno="A"
    endif
    if orderno> (26*26*10*10*10*10) then
    newno=newno & chr$(65+(orderno-(26*26*10*10*10*10)/26))
    orderno=orderno-(26*26*10*10*10*10)/26))
    else
    newno=newno & "A"
    endif
    if orderno> (26*10*10*10*10) then
    newno=newno & chr$(65+(orderno-(26*10*10*10*10)/26))
    orderno=orderno-(26*10*10*10*10)/26))
    else
    newno=newno & "A"
    endif
    newno=newno& format(orderno,"0000")


    note this is indicative, I haven't trested it, written it on the fly here, so the details may be wrong but the basic logic flow shouldbe ok. You may eneed to adjust factors etc.....

  4. #4
    Join Date
    Mar 2004
    Location
    belgium
    Posts
    290
    always ask yourself is the autonumbering necessary, besides its easyness.

    in this case you could solve this by using a textfield and some arithmatic and stringmanupulation,

    befor each append, find the lastrecord (movelast)
    split the string in first part ("AAA") and second part val("999")

    increase second part with 1

    check max value (999)

    if greater change first part to "AAB" and second part to "0001"

    combine first and second part

    append record
    and assign the newly created string to the proper field.

    it´s just an idea.

Posting Permissions

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