An interesting problem

The pricing program for PC (WPP4) has been behaving itself nicely. I've been working on a couple of minor improvements, including being able to create a file on mouldings that can be imported into the new Android App.
That was until a framer rang me up with a problem concerning the Invoice number.

The problem -
The invoice number can be increased in "Setup" > "Options". The idea was that you may not want to start with invoice number "1". However, this framer had a (quite reasonable) scenario of changing the invoice number to reflect her financial year, for example "1314001". She saved the new invoice number value and then tried to save an invoice - this produced an error and the invoice couldn't be saved. The problem was made worse because invoice number couldn't now be decreased in the "Options" form.

The reason for the error goes back to the original design of the database some 6 or more years ago. Each column in the database has a data type, for instance the"DateCollected" column in the table "WorkTickets" is datatype "date". Well the datatype of the "InvNum" column in "WorkTickets" is "integer". All well and good you think (well, I did anyway). But type "integer" in this form of database turns out to be "int16", a number made up of 2 bytes which has a maximum value of 32,787. A much better choice would have been "long integer" ("int32"). This number is made up of 4 bytes and has a maximum value of 2,147,483,647.

The Solution -
Once the data type has been set up in the design of the database it is very difficult to change. Certainly trying to change it programatically would be likely to throw up more problems than it solved. So, if you need to use numbers larger than 32,000 for your invoices here is what to do, "Export" your database and open this copy in Microsoft Access.
You will see a list of Tables at the left hand side. Choose "WorkTickets", the "WorkTicket" table and its data is now shown in the main pane. We want to get to the data types, so go to "Views" at the top left and choose "Design View". Now the Field (Column) names and their datatypes are displayed. Click on "InvNum" (second one down) and at the bottom of the screen its Field Properties are shown. The first one is "Field Size" as "Integer". Click on that line and from the drop-down box select "Long Integer" instead. Save the changes and close Access.
Now all that remains is to "Import" the modified database file back into the program. This will throw up a warning saying the database is corrupted, continue to import? (This is because we've changed the structure of the file.) Click "Yes" and the new database will be imported anyway.
You will now be able to use large numbers for your invoices.
Phew!