MS Access Numbering

Discussion in 'OT Technology' started by Pennywise, Jan 5, 2006.

  1. Pennywise

    Pennywise New Member

    Joined:
    Jan 4, 2005
    Messages:
    81
    Likes Received:
    0
    I think this is pretty advanced, and I have been beating my head all day over it.

    I need to create a field titled "ProjNo" in table that is numbered in the following fashion 2006-001. Not tricky if you use autonumber and set 2006 manually as the static value.

    I however want to have it re-index next year, and also pull old data from tables and take the date from a field titled "Dateadded" that actually carries the date in the foramt dd-mm-yyyy.

    Basically I know it is a DMax() function but this datbase was written by someone else so it would be much easier for me to do this on te table side of things, instead of the form side like someone told me to try (I think that would make migrating all the old data over a bear).

    Thoughts?

    Even if some just write something simple with three fields I would be really happy.
     
  2. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Can't be done.

    You can either go record by record and updating the field in question with the date field in another field, or update the whole table with an update query.
     
  3. Pennywise

    Pennywise New Member

    Joined:
    Jan 4, 2005
    Messages:
    81
    Likes Received:
    0
    I beg to differ.
    I got really close today so I know it can be done- some minor tweaks left and I will be there.

    I will post the code when I nail it- I was incrementing by one and pullingthe year from the field, but it kept adding theyear.

    i.e. if the Date field in my first record held the year 2004 I generated "2004-001". My next entry had the year 2006 my number became 20062004-002 and so on until I finally had a number that read 2004200520032005-00x

    Eh, I hate this shit.
     
  4. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Well go for it - personally I think you're attacking the problem the wrong way but good luck.
     
  5. CyberBullets

    CyberBullets I reach to the sky, and call out your name. If I c

    Joined:
    Nov 13, 2001
    Messages:
    11,865
    Likes Received:
    0
    Location:
    BC, Canada/Stockholm, Sweden
    :werd:

    the code will probably look like an abortion gone wrong. then submit it to thedailywtf.com ;)
     

Share This Page