How to number records in data report

[mdtam]

Hi experts,

I'm using Visual Basic 6, MS Access as Database and Data Environment in my Data Report. I would like to give numbers for my selected data. I separated my data and the numbers then used this code.

SELECT Customers.CustomerID, Customers.CompanyName, 
(SELECT Count(*) From Customers As Rank Where Rank.CustomerId <= Customers.CustomerId) AS RecordNumber
FROM Customers
ORDER BY Customers.CustomerID;

The problem is it's not produce sequence numbers. Thanks for your help.

[Rabbit]

It would help to see sample data, what your current code is producing, and what it's supposed to produce instead.

[mdtam]

it's produce numbers but not sequence like
3.
5.
2.

I would like it produces like
1.
2.
3.
until the end.

[Rabbit]

I still need to see the sample data that is producing those results.

[mdtam]

my data looks like this:

CustomerID    Company Name 
  111           kolapis          
  121           nangka            
  131           atec              
  122           vis               
  152           dell

let's say I choose atec,dell and nangka. I would like the numbers in data report sequence 1, 2, 3.

[Rabbit]

There's the issue then. You're filtering for certain companies in your query but not filtering in your subquery as well.