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

1. 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. 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. 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. Re: line number in a query (A2000 SR-1)

Are your data sorted on a unique number field?

5. 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. 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. 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.

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. 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. 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
•