Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Customising an autonumber

Discussion in 'Information Technology' started by Daniel Lees, Jul 28, 2006.

  1. Daniel Lees

    Daniel Lees
    Expand Collapse
    Guest

    I am implementing an access database and need an autonumber field to
    automatically fill in when a new record is created. I need the field to be in
    the following format...

    QN then 5 digit number starting from 06700 or around there then the last two
    digits of the year on the end.

    EG

    QN0676306

    Can any1 help me with this?

    Cheers

    Danny
     
  2. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    Why? As in why are you using a so-called "intelligent key" (where that isn't
    a complimentary thing)?

    You're trying to store 3 separate pieces of information in a single field.
    That's a violation of database normalization principles. You should store
    them as 3 separate fields. You can always create a query that concatenates
    them into a single value for display purposes, and use the query wherever
    you would otherwise have used the table.

    If you must, though, you'd put logic in the form's BeforeInsert event to
    check what the highest value used to date is, and assign the next number.

    Something like the following untested air-code:

    Dim strNextNumber As String
    Dim varLastNumber As Variant

    varLastNumber = DMax("IDField", "MyTable", "IDField ='QN?????" &
    Format(Date(), "yy") & "'")
    If IsNull(varLastNumber) Then
    strNextNumber = "QN06700" & Format(Date(), "yy")
    Else
    strNextNumber = "QN" & _
    Format(CLng(Mid(varLastNumber, 3, 5)) + 1, "00000") & _
    Format(Date(), "yy")
    End If

    This assumes that you want to restart numbering at 06700 each new year.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Daniel Lees" <DanielLees@discussions.microsoft.com> wrote in message
    news:7C50F8FA-CFAA-4506-B49A-9A7C4C0FD50A@microsoft.com...
    > I am implementing an access database and need an autonumber field to
    > automatically fill in when a new record is created. I need the field to be

    in
    > the following format...
    >
    > QN then 5 digit number starting from 06700 or around there then the last

    two
    > digits of the year on the end.
    >
    > EG
    >
    > QN0676306
    >
    > Can any1 help me with this?
    >
    > Cheers
    >
    > Danny
    >
     
  3. BruceM

    BruceM
    Expand Collapse
    Guest

    Does the five digit number reset each year? If not, see this link for one
    way of adding an incremented number to new records:
    http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb
    The simplest thing would probably be to enter 6700 in the field (I will call
    it YourID) for the first record, then implement Roger's method for
    successive records.
    If there is a date field in the record (I will call it YourDateField) then
    it is just a matter of adding something like the following to the Control
    Source of an unbound text box:
    = "QN" & Format([YourID],"00000") & Format([YourDateField],"yy")
    There is no need to store the full number. It can be displayed whenever you
    like.
    If a form or report is based on a query, you could put this into the top of
    a blank column in the query design grid:
    CompleteID: "QN" & Format([YourID],"00000") & Format([YourDateField],"yy")
    Then you can use CompleteID as the Control Source of a text box.
    Substitute your own field names, of course.

    "Daniel Lees" <DanielLees@discussions.microsoft.com> wrote in message
    news:7C50F8FA-CFAA-4506-B49A-9A7C4C0FD50A@microsoft.com...
    >I am implementing an access database and need an autonumber field to
    > automatically fill in when a new record is created. I need the field to be
    > in
    > the following format...
    >
    > QN then 5 digit number starting from 06700 or around there then the last
    > two
    > digits of the year on the end.
    >
    > EG
    >
    > QN0676306
    >
    > Can any1 help me with this?
    >
    > Cheers
    >
    > Danny
    >
     
  4. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    Private Sub Form_BeforeInsert(Cancel As Integer)
    Dim Db AS DAO.Database
    Dim Rs As DAO.Recordset
    Dim thQ As string
    Dim MaxNo as Long

    Set Db = Access.CurrentDb
    thQ = "SELECT MAX(Mid(A.QN,3,5)) AS MaxNo FROM TheTable A WHERE A.QN Is
    Not Null AND Right(A.QN,2) = Format(Date(),'yy')"
    MaxNo = Nz(Rs.Fields(0).Value,6699) + 1
    Rs.Close : Set Rs = Nothing
    Me.QN.Value = "QN" & Format(MaxNo,"00000") & Format(Date(),"yy")
    set Db = nothing

    End Sub

    HTH

    Pieter

    "Daniel Lees" <DanielLees@discussions.microsoft.com> wrote in message
    news:7C50F8FA-CFAA-4506-B49A-9A7C4C0FD50A@microsoft.com...
    >I am implementing an access database and need an autonumber field to
    > automatically fill in when a new record is created. I need the field to be
    > in
    > the following format...
    >
    > QN then 5 digit number starting from 06700 or around there then the last
    > two
    > digits of the year on the end.
    >
    > EG
    >
    > QN0676306
    >
    > Can any1 help me with this?
    >
    > Cheers
    >
    > Danny
    >
     
  5. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    Private Sub Form_BeforeInsert(Cancel As Integer)
    Dim Db AS DAO.Database
    Dim Rs As DAO.Recordset
    Dim thQ As string
    Dim MaxNo as Long

    Set Db = Access.CurrentDb
    thQ = "SELECT MAX(Mid(A.QN,3,5)) AS MaxNo FROM TheTable A WHERE A.QN Is
    Not Null AND Right(A.QN,2) = Format(Date(),'yy')"
    MaxNo = Nz(Rs.Fields(0).Value,6699) + 1
    Rs.Close : Set Rs = Nothing
    Me.QN.Value = "QN" & Format(MaxNo,"00000") & Format(Date(),"yy")
    set Db = nothing

    End Sub

    HTH

    Pieter

    "Daniel Lees" <DanielLees@discussions.microsoft.com> wrote in message
    news:7C50F8FA-CFAA-4506-B49A-9A7C4C0FD50A@microsoft.com...
    >I am implementing an access database and need an autonumber field to
    > automatically fill in when a new record is created. I need the field to be
    > in
    > the following format...
    >
    > QN then 5 digit number starting from 06700 or around there then the last
    > two
    > digits of the year on the end.
    >
    > EG
    >
    > QN0676306
    >
    > Can any1 help me with this?
    >
    > Cheers
    >
    > Danny
    >




    --
    ----------------------------------------
    I am using the free version of SPAMfighter for private users.
    It has removed 4285 spam emails to date.
    Paying users do not have this message in their emails.
    Get the free SPAMfighter here: http://www.spamfighter.com/len
     
  6. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    Daniel Lees wrote:
    > I am implementing an access database and need an autonumber field to
    > automatically fill in when a new record is created. I need the field
    > to be in the following format...
    >
    > QN then 5 digit number starting from 06700 or around there then the
    > last two digits of the year on the end.
    >
    > EG
    >
    > QN0676306
    >
    > Can any1 help me with this?
    >
    > Cheers
    >
    > Danny


    The others have provided very good answers for you.

    I will only add one thing that I did not see noted even thought it was
    addressed in the suggested code. Autonumbers should not be used for human
    consumption. They are designed only to provide unique numbers, not
    consecutive numbers and when they skip numbers it tends to cause
    considerable confusion on the part of users of the database.

    --
    Joseph Meehan

    Dia duit
     

Share This Page