Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2014

    Unanswered: Add counter field for an Invoice

    I have a table/query to issue invoices to a customer.
    But I don't want to count all records, I want to count/assign numbers for each record only within a given Invoice. So that I can number each invoice line item. What's the secret to doing this?

  2. #2
    Join Date
    Nov 2011
    Are you trying to do this via a qry or on a form? You could qry for Invoices using that Invoice number. I don't understand your Schema of course. Why is the Line Item not auto numbered?

  3. #3
    Join Date
    Mar 2014


    Let me explain a bit better:
    - I am inputting to a form, which is feeding back to a query.
    - Example - You have a table that manages the raw data of many invoices. The data all has a primary key of "Invoice #" to organize the data. But, within each invoice, I want to organize my invoice detail by line item. So when you scan in a product for a given invoice, I want first scan to be numbered "001", 2nd item to be numbers "002".

    The counter will count/order ALL the data in the table. but I want to count/order just the data within a given subset of data, without manually inputting the number.

  4. #4
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    I do hoip you are not using 'invoice #' as your column name... # is a reserved symbol and may well cause you problems.

    001 is a formatting issue, not a numbering issue, 001 is a text representation of a number.

    Assuming you want contiguous numbers starting from 1, short of leaving this up to your users there is not an easy fix for this.

    presuming that only one person will be entering order details on a specific invoice at the same time (you can have multiple people entering invoices at the same, BIT ONLY one per invoice), this should work

    lets assume
    you have a column called 'LineNo' which is mapped to a text box control called tbLineNo.
    tbLineNo is locked (so users cannot add/edit or change
    your invoice number in the parent form (MyInvoiceForm) is in a control called tbMyInvoiceNo)

    in the first control that a user would enter data place some code that finds the next available number for that invoice number place soem code which calls the function below to get the next available line no for the current invoice
    eg in the forms before insert event
    tbLineNo.value = GetLineNo(forms!MyInvoiceForm!tbInvoiceNo)

    Private function GetLineNo(InvoiceNo as integer) as integer
    'function to return the next available line no for a specified invoice number
    if the invoice number is invalid or not found then return NULL,mas the user is playing silly.....
    'OK so how can we validate the invoice number
    if not isnull(dlookup("MyInvoiceNo","MyInvoices", "MyInvoiceNo =" & tbInvoiceNo)) then 'we found the invoice in the invoices table
      'ok so we found the invoice in the invoices table, now we need to find the current maximum line number for this invoice
      getlineNo = dmax( "MyLineNo", "MyOrderDetails","MyInvoiceNo = " tbInvoiceNo)
      if isnull(getlineNo) then this is the first line for this invoice
        GetLineNo = 1 'set up or default return value
            GetLineNo  =     GetLineNo  + 1 'add one tot he current value
    else 'we didn't find the invoice number in the invoices so return null (effectivley not found)
      getlineno = null
    replace any reference to my.... with whatever the correct name for your columns/tables
    if you have been unfortunate enough to use spaces in your column & table names delimit those using square brackets, eg:-
    [my table].[my column]
    or better yet do the right thing and redesign the db right now and get rid of this dirty habit.. spaces are a reserved symbol in table/column/form/report (in fact any object

    you can use underscore or CamelCase to separate words

    the abive code is air code, its untested, untried, will almost certainly contain spellign mistakes, typos, may contain logic errors, may not compile. you will need to work out how to clear thise faults yerself....
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2009
    Provided Answers: 14
    With a very simple Table used for the example:
    CREATE TABLE [Tbl_Sales]
        ( [SysCounter] INTEGER NULL CONSTRAINT PrimaryKey PRIMARY KEY,
          [Item Description] TEXT(50) NULL,
          [Quantity] INTEGER NULL,
          [InvoiceNumber] INTEGER NULL
    You can write:
      SELECT a.SysCounter, 
             a.[Item Description], 
             ( SELECT Count(SysCounter)
                 FROM Tbl_Sales b 
                WHERE b.SysCounter <= a.SysCounter AND
                      b.InvoiceNumber = 1
             ) As ItemNumber
        FROM Tbl_Sales AS a
       WHERE a.InvoiceNumber = 1
    ORDER BY a.SysCounter;
    Have a nice day!

Posting Permissions

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