Results 1 to 4 of 4
  1. #1
    tony6562 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Apr 2012
    Posts
    4

    Autonumber problem

    I am developing a database for a small company but I want the invoice number to be automatically produced into a format of MM/YY/0000 but I also want the numbering to zero automatically when the month or year changes, any help would be greatly appreciated, I realize that this may need some coding so thats why I need the help. Thanks.

  2. #2
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Hi -

    First, I suggest you not have all the data in one field - you have the date in the Invoice Date (I hope!), so all you need is the invoice number. You can easily get the "Next" number for a particular month with the DMax function. Assume your Table is called Invoices, the Invoice Date is in a field called InvDate, and the Sequential number is called InvNumber.

    The next sequential number for the current invoice with CurrentInvoiceDate is:

    NextNumber = nz(dmax("InvNumber","Invoices","Year(InvDate) = " & year(CurrentInvoiceDate) & _
    " AND month(InvDate) = " & month(CurrentInvoiceDate) ), 0 ) + 1

    Whenever the month or year changes, DMax will return Null the first time - the Nz function detects that and changes Null to 0, so you get 1 as expected.

    To format the number as required for reports etc, use:

    Format(InvDate,"MM/YY") & "/" & format(invnumber,"0000")

    Edit: If MS Access insists on giving you a dash between MM and YY (e.g. 04-12), try this:

    Format(InvDate,"MM\/YY\/") & format(invnumber,"0000")


    HTH

    John
    Last edited by John_G; 05-14-2012 at 02:27 PM. Reason: Format function as shown may not work

  3. #3
    tony6562 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Apr 2012
    Posts
    4
    Hi John_G,
    Thanks for your help it works a treat, I think I just had a large mental block, must be my age.

    Cheers
    Tony Carpenter

  4. #4
    tony6562 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Apr 2012
    Posts
    4
    Hi John,

    I thought I had this sorted but it is still cause headaches could you give me a small example database to the solution, sorry if a bit of a pain but it is really important that I get this as it is the only thing now holding me up to get the database up and running.

    Also had another quick thought about the same problem, is the InvNumber field going to cause an duplicate errors after the first month and so on?.

    Thanks, hope you don't mind.

    Tony Carpenter

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Help with autonumber
    By GDubbs780 in forum Programming
    Replies: 15
    Last Post: 02-27-2011, 02:27 PM
  2. Filter on Autonumber
    By jgelpi16 in forum Programming
    Replies: 3
    Last Post: 01-24-2011, 10:46 AM
  3. Autonumber with Text
    By jgelpi16 in forum Access
    Replies: 6
    Last Post: 01-21-2011, 02:36 PM
  4. Autonumber Problem in adp project
    By dneruck in forum Access
    Replies: 8
    Last Post: 04-22-2010, 09:43 AM
  5. AUTONUMBER
    By J A F F A in forum Database Design
    Replies: 1
    Last Post: 10-03-2007, 10:30 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums