Auto increment in a query?

[LikeThisName]

i need a field generated in a query (or so i think).

the count() function only gives me a total, i need a field that basically numbers the rows returned.

thanks in advance.

 

[DanJR]

You could probably do it using a subquery, providing that you have a group of fields that uniquely identify each record in the query and there is some sort of order within the rows.

Perhaps you could show us the SQL Statement...

Cheers,
Dan

 

[DanJR]

STATIC VARIABLES
----------------
using static variables can be a little dangerous if you view the data, because each time you click on a row the 'function' will recalculate and thus may give the wrong answer if you don't click on the rows in order. Declaring a variable or function as static means the variable will remember its variable between calls to the sub or function.
[tt]
e.g.
Public function Add2() as long
Static lng as long

lng = lng +2
add2 = lng

end function
[/tt]
The first time you call this function it should return 2, but the next time it will return 4,...etc

I have used this technique, but only when calling update quering.

SUB-QUERY
---------
consider the example data:
[tt]
myID Code
1 A
5 E
6 V
8 A
9 B

The following subquery:

SELECT
myTable.myID,
myTable.Code,
(SELECT Count(*) FROM myTable as A WHERE A.myID <= myTable.myID) AS [Count]
FROM
myTable
ORDER BY
myTable.myID


will give:

myID Code Count
1 A 1
5 E 2
6 V 3
8 A 4
9 B 5
[/tt]
The disadvantage is that the query becomes non-updatable.

In a form, where you are displaying the data as a continuous form, then there is another way - by calling a function from a public function:
[tt]
Public Function RecordPosition(RowID As String, RowValue As Variant, f As Form) As String
'Determines to position of the record in the recordset


Dim rs As DAO.Recordset
Dim strCriteria As String

On Error GoTo errHandler

'create search criteria
If IsNumeric(RowValue) Then
strCriteria = &quot;[&quot; & RowID & &quot;] = &quot; & RowValue
Else
strCriteria = &quot;[&quot; & RowID & &quot;] = '&quot; & RowValue & &quot;'&quot;
End If


'find position of RowValue in recordset
Set rs = f.RecordsetClone

If Not (rs.EOF And rs.BOF) Then
rs.MoveFirst
rs.FindFirst strCriteria

If rs.NoMatch Then
RecordPosition = &quot;*&quot;
Else
RecordPosition = (rs.AbsolutePosition + 1)
End If

Else
RecordPosition = vbNullString
End If

errHandler:

End Function
[/tt]

The function can be called by from a textbox in the form using the control source property:
[tt]
=RecordPosition(&quot;SampleID&quot;,[SampleID],[Form])
[/tt]
in this case the field &quot;SampleID&quot; was unique and the data was sorted by &quot;SampleID&quot;. the [Form] part simply passes a reference of the form to the function (i.e., You don't change this bit)

I hope this helps.

Cheers,
Dan