Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2003
    Posts
    69

    Unanswered: Autocount but not all records

    Hi ,

    I have this table with order numebrs and invoice numbers. Order numbers are of type autocount (?) (autonummering) and they follow up nicely. Now i want to do same for the invoicenumbers but not for all of them.

    e.g.

    I have order 1,2,3,4,5,6,7,8,9,10 and i want order 2 to be invoice number 1 and if i want to change order 4 in an invoice it should be automaticall 2 and so further.
    Is there any function in acces where i can get the biggest value in ALL records of one column in one table so i can increase it by one?
    something like: invoicenumber= get max([table].[invoicenumber])+1

  2. #2
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369
    strSQL = "(select top 1 table1.ID from table1 order by table1.ID DESC)"

    Returns 1 record/1 table field with the latest number.

    D.
    Win-XP pro, Access 2002, ADO 2.7, DAO 3.6. English versions of apps/OS.

  3. #3
    Join Date
    Sep 2003
    Posts
    69
    Thank you for helping me but my syntax is not correct, can you please help me a little more?
    This is what i have:



    Private Sub Knop130_Click()

    Dim strsql As Integer

    strsql = (select top 1 [BESTELLINGEN].[Factuurnr] from BESTELLINGEN order by [BESTELLINGEN].[Factuurnr] DESC)

    factuurnr = strsql + 1

    End Sub

    **************************************************
    BESTELLINGEN is the name of the tabel
    Factuurnr is the name of the record containing the invoice number
    **************************************************

  4. #4
    Join Date
    Sep 2003
    Posts
    69
    Or is it maybe the function seltop you meant by select top ?

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Check out the DMax() function in the help file.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Sep 2003
    Posts
    69
    You are a genie. This is exactly what i needed. Thank you!

    Isn't there a site with all vb functions with examples? I can imagine no one can memorize all vb functions.

  7. #7
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    there are a few sites, but you'd be suprised how many you remember when you do this stuff everyday.

    Try this for starters:
    http://msdn.microsoft.com/library/de...RefTopNode.asp

    btw: msdn.microsoft.com is a very good resource for any of microsoft's products - I generally start there, and if I can't find it there within 5 minutes, I'll jump to to forum or web.

  8. #8
    Join Date
    Sep 2003
    Posts
    69
    ok , thanks for your help and support

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    FYI: All of the DOCUMENTED functions (the ones you'll most likely use) are available in the help file of the CODE EDITOR. usually it gives examples and whatnot. Extremely useful, I hit it up at least once a day.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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