Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2003

    Question Unanswered: Formating "AutoNumber" question

    I was wondering if anyone knows if its possible to format a "autonumber" field using a conditional statement.

    For example my current ID is formated like: 00001, 00002, 00003 etc...

    I have a column called Deparment that can have 1 of 2 possible entries. If it is the first entry i would like a "C" added to the autonumber to make it "C0001" if it is the second entry, I would like it to be "A0001".

    Is this possible??

    Thanks for your input.

  2. #2
    Join Date
    May 2003
    Provided Answers: 5
    not in an autonumber defined field but you can d\create a compund key

    for exqmple:
    myid = 0001
    mydept = c
    mycompoundkey = mydept & myid

    autonumbers are exactly that - assigned integers

    you can also try using the autonumber but not set it as the primary key
    when you add a record the autonumber increments and then concantonate any other string to that number and store that value as you primary key

    as in the example above

  3. #3
    Join Date
    Dec 2002
    Glasgow, UK
    I use a similar code in one of my DBs:

    Do Until rst.EOF = True
    If rst!AXIS_Id < 10 Then
    rst!Mailing_ID = "NHS00000" & rst!AXIS_Id
    ElseIf rst!AXIS_Id > 9 And rst!AXIS_Id < 100 Then
    rst!Mailing_ID = "NHS0000" & rst!AXIS_Id
    ElseIf rst!AXIS_Id > 99 And rst!AXIS_Id < 1000 Then
    rst!Mailing_ID = "NHS000" & rst!AXIS_Id
    ElseIf rst!AXIS_Id > 999 And rst!AXIS_Id < 10000 Then
    rst!Mailing_ID = "NHS00" & rst!AXIS_Id
    ElseIf rst!AXIS_Id > 9999 And rst!AXIS_Id < 100000 Then
    rst!Mailing_ID = "NHS0" & rst!AXIS_Id
    Else: rst!Mailing_ID = "NHS" & rst!AXIS_Id
    End If


    the field axis_id id the autonumber for the table.

    this will work for up to 9,999,999 records (although access would collapse up before you ever got that far!)

Posting Permissions

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