Blank Data

[Bill]

Hi,

I have about 5000 product number. There are 2530 Blank product number when I open the query. I just want to create new number (like below 999999 01) to replace blank product number only but the rest of it should be remain the same.

How I make the count like default on query
999999 01 then count next number
999999 02
999999 03
999999 04
etc.,

Your help would be much appreciated....

 

[Tom Ellison]

Dear Bill:

Likely, you do not want to to duplicate any of the existing 2470
product numbers. So, are you certain the new ones you propose to
assign won't duplicate existing ones?

Next, you cannot generate 2530 product numbers starting with 999999 01
and incrementing by one each time. What I'm suggesting is that you
may need to number them beginning with 999999 0001, or something like
this, so you have room for 2530 new numbers.

Finally, before doing this, you will need a column, or set of columns
that are unique within the 2530 rows with blank numbers. This way we
can create a unique ranking value to number the 2530 rows.

However, this technique uses an aggregate to COUNT() the rows
preceding each target row. Unfortunately, if you use an aggregate in
an UPDATE query you just get an error. So, I'm afraid this will
prevent a simple solution.

So, I would go to using a recordset in VBA to loop through all the
blank rows and a counter to assign the successive values.

Clear as mud? Well, I think it is a workable outline. Not trivial,
but intermediate level stuff.

Hi,

I have about 5000 product number. There are 2530 Blank product number when I open the query. I just want to create new number (like below 999999 01) to replace blank product number only but the rest of it should be remain the same.

How I make the count like default on query
999999 01 then count next number
999999 02
999999 03
999999 04
etc.,

Your help would be much appreciated....

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts