Ms access query - add a serial/record number?

[matthewra]

Can I add a sequence number/serial number to any MSaccess query?

Select * from A
where ...
order by ...


e.g. A has 3 fields, A1, A2, A3

Select a1,a2,a3 from A where a1 <> "x"
order by a2

and have 4 columns including a serial number for the records....

 

[matthewra]

I used this:

http://support.microsoft.com/kb/94397

how can query call the SetToZero() first

this is for novice users who will want to add the capability to a large set of yet to written queries

 

[kzutter]

The article you are referring implies that the SetToZero routine is called BEFORE you run the query. This could be accomplished with either a macro or vba code (perhaps behind a button on a form).

I cannot think of any way that it could be done within the query itself.

 

[formerTexan]

Is your sequence number intended to be globally unique, unique to the session or just unique within the recordset? Does your numbering need to be sequencial and incremental? Answering this will determine whether you need to reset the global variable, ignore the global variable, change the variable scope or think about something else altogether.

Cheers, Bill

 

[matthewra]

Unique for the query.
Needs to sequential and incremental.

Now it starts at +1 number of records and is incremented by1 on each record.
i want it to start at 1 for each query

 

[formerTexan]

Hello again Matthew,

Solution for you.

First, change

Global Cntr

to

Private Cntr as long

This will reduce the variable's scope and prevent it from being inadvertantly referenced somewhere else.

Second:

Call the SetToZero function (in addition to the QCntr function) from your query.

Blah: SetToZero()

Note: Don't hide this field.

Cheers, Bill