none
VB.NET and MS-Access SQL MAX Statement in Column(TEXTFIELD)

    Question

  • guys, can you help me how to increment a Invoice Number when it is a Text Format in ms-access...

    Example:

    JPS1
    JPS2
    JPS3
    JPS4
    JPS5
    JPS6
    JPS7
    JPS8
    JPS9
    JPS10
    JPS11
    ...JPS1000000

    Im using ms access 2007 and Visual Studio(Visual Basic) 2008
    • Edited by ranohj Friday, January 11, 2013 12:37 AM
    • Moved by Cindy Meister MVPMVP Friday, January 11, 2013 7:08 AM access-specific issue
    Friday, January 11, 2013 12:34 AM

Answers

  • Following SQL Statement should deliver the next free Invoice Number. Be aware that this is not multi-user capable. So when you insert the record you may get a duplicate key error and have to get a new Invoice Number with the same statement until it doesn't fail anymore. In addition: The first Invoice Number you should add manually as this statement will probably not work on an empty table.

    SELECT 'JSP' & MAX(MID([InvoiceNo],4)+1) AS NextInvoiceNo
     FROM Invoices;

    BTW: Access2007 is not the database engine, this is JET (MDBs) or ACE (ACCDBs). The prefered access method is DAO.

    HTH
    Henry

    "ranohj" schrieb im Newsbeitrag news:4fdde2a8-ed74-4c8d-bc33-5fa3334fd803@communitybridge.codeplex.com...

    guys, can you help me how to increment a Invoice Number when it is a
    Text Format in ms-access...
    Example:
    JPS1
    JPS2
    [..]
    ...JPS1000000
    Im using ms access 2007 and Visual Studio(Visual Basic) 2008

    Friday, January 11, 2013 8:03 AM