Autonumber in a query

[sunilvedula]

Hi All,

i have a query which gives me a result of set of records. I want it to be auto numbered for making it random. I have tried several ways to increment by writing an expression but something goes wrong. For ex in the queyr that i have written like

SELECT TblAccq.Pan, TblAccq.Queue, TblAccq.EmpID, TblAccq.Switch, TblAccq.REVDRAMT, TblAccq.STLMTAMT, TblAccq.[CURRTIME ], TblAccq.Action, TblAccq.Inputdt1, TblAccq.Area, TblAccq.Reasoncode, TblAccq.ATM, TblAccq.Time, TblAccq.Status, IncrementValues() AS Expr1
FROM TblAccq
WHERE (((TblAccq.Queue)=FQ()) AND ((TblAccq.EmpID)=FID()) AND ((TblAccq.Inputdt1)>=FSDATE() And (TblAccq.Inputdt1)<FEDATE()) AND ((TblAccq.Status)="C"));

this give me a number like 1 but it repeats the same for all the records. I just want them to be autonumbered like 1,2,3 etc....
Iwrote these function which i called in query and before i run the query:
Function IncrementValues() As Integer
IncrementVariable = IncrementVariable + 1
IncrementValues = IncrementVariable
End Function
Function ResetCounter()
IncrementVariable = 0
End Function

Can anyone let me know what i can do to get 1, 2, etc.. for all the records displayed in the query and i am not interested in inputting them in a table.

 

[namliam]

For a function to be called on every line of the query you need to send a column into the function, even if inside the function you dont do anything with it...

 

[sunilvedula]

i got the solution. i took the result of this query into another query and there i caluclated the expression 1+DCount("Sno","qrysearch","[Sno]<" & CStr([Sno]))

This gives me the numbers for all the records listed.

 

[namliam]

DCOunt is Ultimatly slow though

 

[Atomic Shrimp]

That's pretty cool - it only really works if 'Sno' is an autonumber and you want the records counted in the same sequence as the autonumber, but it does work even if there are gaps in the autonumber sequence.