>We have two free tables that are related by invoice number, InvoiceA, and InvoiceB. InvoiceA contains the accounting information for a customer's invoice. InvoiceB contains the line items.
>
>Both contain invoice number fields that are 5 digits long (they're numeric fields). InvoiceA is indexed on the Invoice field but InvoiceB is indexed on the STR(Invoice,5)+CATALOGNO. That way, invoices appear in the proper alphabetical order (by catalog number) on the invoice and on computer screens.
>
>We are about to reach the rollover of 99,999 to 100,000 and have converted the invoice number fields from 5 digits to 6. InvoiceA and InvoiceB are now related by STR(Invoice,6). InvoiceB is indexed on STR(Invoice,6)+CATALOGNO.
>
>Since the programs are designed for 5 digit invoice numbers there are bound to be many problems. Is there a graceful way of minimizing them?
>Thanks in advance.

Ugh. My condolences. Looks like you've got a lot of work to do. I can't think of anything to help simplify things.

Although, one thing to consider would be re-using the numbers. Of course, you'd need to add another field (e.g. "fiscal year") and then clear out last year's invoice data, then restart at zero. You'd also have to include the year in forms, reports, etc. (e.g. 99-12345, 00-12345). Could well be more trouble than fixing your 5- to 6-digit problem.

If I was designing this from scratch, I'd use Integer for the Invoice #'s (of course, these would not be the primary/foreign keys, separate Integer fields would be used for the PKs and FKs). I'd be inclined to use a parameterized view for the invoice details. The view can be indexed in the form's .Load() so it always appears in Catalog order, even as new items are entered.