Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Kansas, USA
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    line number in a query (A2000 SR-1)

    Does anyone have a bit of code for a function that would create sequential numbers that could be inserted into a query. I have a query that I export to a file where I want to generate line numbers like 1, 2, 3, 4, 5, etc.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: line number in a query (A2000 SR-1)

    Although there are solutions, they often work only in certain circumstances. In a report: you can put a text box in a section with Control Source set to =1 and Running Sum set to Over All. If exporting a report is a viable alternative, it is much easier than using cumbersome custom functions in a query.

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    Kansas, USA
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: line number in a query (A2000 SR-1)

    Thanks, Hans, but I need to export a comma delimited file that has to have line numbers on it.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: line number in a query (A2000 SR-1)

    Are your data sorted on a unique number field?

  5. #5
    Star Lounger
    Join Date
    Jan 2001
    Location
    Kansas, USA
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: line number in a query (A2000 SR-1)

    No, they are not sorted. They are order detail lines that I want to import into another program via a text file, but they need to be numbered. I'm thinking of some kind of function where you would say something like, LineNumber = LineNumber + 1 and it just keeps growing.

  6. #6
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: line number in a query (A2000 SR-1)

    People here will go running and screaming from my non-classy way of solving this problem, but here's what I would do in your case, given the difficulty:

    After readying your table or query for export (sorting, etc.), I would export it to a new table that also happens to have an autonumber field -- for example, name this the "SalesForExport" table, and then export from that.

    thx
    Pat

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: line number in a query (A2000 SR-1)

    Try this:

    Create a standard module by clicking New in the Modules section of the database window.
    Copy the following code into the module:

    Public lngNum As Long

    Public Function LineNum(AnyVal) As Long
    lngNum = lngNum + 1
    LineNum = lngNum
    End Function

    Open your query in design view.
    Add a calculated column:

    LineNumber: LineNum([AnyField])

    where AnyField is the name of an arbitrary field in the table.

    As you will find out, the first time you run the query in a session, the line numbers will start at 1, but for each subsequent run, they will start at the previous highest line number + 1. If you want the line numbers to start at 1 again, you must reset the lngNum variable to 0 in code before opening the query.

  8. #8
    Star Lounger
    Join Date
    May 2002
    Location
    Patagonia Region Chile
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: line number in a query (A2000 SR-1)

    I have a similar need. I have a query that has InvoiceID and INVdetailnum I'd like to add a field that essential assings a line item number 1,2,3 etc to each Invdetail num so in the end you can see that invdetailnum 136 id item 3 of invoice 17. So. the querry should sort first on InvoiceID, then on INVdetailnum, and the Linenum fields starts at 1 and goes up by one until it sees that its hit a record with a new InvoiceID so it starts at 1 again and counts up by one.... Any hope for me.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: line number in a query (A2000 SR-1)

    You can do this in two steps:

    1. Create a query that sorts on InvoiceID first and then on INVDetailNum. You can add other fields if you need them. Save this query as, say, qrySortedInvoices.

    2. Create a new query based on qrySortedInvoices. Add the fields from the query (or *), plus a calculated column:

    <code>LineNum: Val(DCount("*","qrySortedInvoices","InvoiceID = " & [InvoiceID] & " AND INVDetailNum <= " & [INVDetailNum]))</code>

    Note: some browsers mess up the "less than or equal" in the above expression. The end should be

    <code>" AND INVDetailNum < = " & [INVDetailNum]))</code>

    but without a space between < and =

Posting Permissions

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