line number in a query (A2000 SR-1)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

Are your data sorted on a unique number field?

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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:

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

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

" AND INVDetailNum < = " & [INVDetailNum]))

but without a space between < and =

Reply | Quote

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:

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

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

" AND INVDetailNum < = " & [INVDetailNum]))

but without a space between < and =

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Are your data sorted on a unique number field?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote