Runtime SELECT QUERY Autonumber 1

[Savil]

I need to have a field in a select query that creates a unique number for each row, something like an autonmber that is created at runtime. Has anybody got a solution to this?

Thanks

 

[PHV]

Do a search for rank[/i]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lupins46]

In a select query, you can only do this if you already have a a unique field in the table and you are sorting the result on this field.
You can't just number the rows in a select query (you can get the start of a number sequence but each time you click into a record it will recalculate and so lose the sequence).

You can number the rows in a make table query if that is your intention.

You can number the lines in a report if that would be of any use.

 

[Savil]

Just to expand a little I have to do a regular export to text file of approx 5000 records which is then sent elsewhere. The text file has to be fixed width and meet strict data length criteria. The field in question has to be a unique value of four characters long, it can be absolutely anything but must be four charachters long. The table that supplies this query has an autonumber field which I thought I could use the last four characters and pad out with zeros if length is less than four. After a bit of thought it is possible that over time records could have the same last four characters.

Hope thats enough info

 

[lupins46]

If you are exporting 5000 records at a time then a four digit number is going to repeat on alternate exports. I don't see how what you are asking is feasible.

 

[Savil]

They have to be unique per export, what you are saying is not an issue.

 

[lupins46]

Then you can use this:

In a new module put:

Dim x

Function NextNum(vDummy)
x = x + 1
NextNum = x
End Function

Function zerox()
x = 1000
End Function

Now create query which will be used for the export.
Include two extra field as the first two columns in the query:
zz:Zerox()
myNum:NextNum(myfieldnamehere)

zz will initialise the number to 1000; you can untick the Show box for this column.
MyNum is the serail number. You should put any valid field name in the brackets.

This will now work for export purposes.

However if you view the query the serial number will not work correctly because recalculation will occur as you move around the records and it will produce spurious values.