Is there a way to indicate a skip in sequence on a report?

[rookiedev]

I was wondering if it was possible to create a report that would indicate missing number like your checking statement does. I have of course, an inventory listing and was looking for an easy way that I could find out what numbers were available. The Product Names start with 1000 and go through 9999 and I have found many numbers have been deleted over the years and would like to assign new products to the missing numbers.
Any one have any suggestions?
RookieDev

 

[GDGarth]

Here's what I've done, in similar situations:

I create a new table, with only one field. That field contains all the possible numbers that could exist in your other table (in your case, 1000 to 9999). The easiest way to create this table is in Excel, then link it.

Then, use the "Find Unmatched Query Wizard" to find the numbers that aren't used.

 

[rookiedev]

That works fine. Is there any way to have an indication placed on a report like a checking statement?
RookieDev

 

[GDGarth]

I use this to check for null values. Substitute your field and message in the code. I put the code in the "on format" event of the detail section of the report. TestText is a text box I use to display the message.


Dim tt
tt = [Your Field Name]
If IsNull(tt) = True Then [testtext] = "Missing Record"
If IsNull(tt) = False Then [testtext] = "Not Null"

 

[GDGarth]

I forgot to post - to use this, your query needs to show all values for "ProductNames", whether they've been assigned or not. Then you can check for Null values in one of the other fields.

 

[MichaelRed]

but ... re-using values like this CAN lead to other problems. Suppose a "product" is refered to in other ways / apps / old customers (buying repair or replacement items). The re-cycled value would point to an 'incorrect' item.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[GDGarth]

MichaelRed:

Good point.

I agree - I wouldn't advise reusing numbers under most circumstances, either. Numbers are cheap - use a new one.

The original poster may have a legitimate reason for wanting to do this. If so, there is a way to make it work the way he wants to. He'll have to decide whether that makes sense for his application.

 

[rookiedev]

Thanks for the great advice! Here is the reason I am trying to seek out the missing numbers....I converted the data from a corrupted DB3 database and I want to make sure everything is accounted for.
There would actually not be a record for a missing product and therefore all of the transactions would also be missing.
RookieDev