Hi Everyone

I have done a blunder on my database:

When I built it I gave the MaterialsInventory# a field size of 3 characters, which I soon got to learn (stupid me for not thinking it through) has to be a lot bigger. I have now extended it to 5 characters which should be more than enough. Now, when my list comes up its not organized properly, 123 would com after 12299 for instance and so on.

my question is; how can I make a function/code/something-smart to extend the 123 type values to 00123? This table is linked as well so I guess I'd have to delete the links and do the same for every linked table? I got about 5000+ entries so don't feel like doing this manually:P


Any help would be highly appreciated!


Cheers

Looks like you definied the field to be a text field. If you defined it as a number instead (Long Integer maybe) it would sort correctly.

yes that is correct...


So what you're saying is that it is too late or is it solvable?



cheers

Erm, no. It's not too late at all. First make a backup of your db. Then open your table in design view. Change the field type to Number and save the table. If it prompts you that existing data already violates this new type, then check those entries and make sure that they're not accidentally including non-numeric values. Fix so they're numeric, then try again. You may also want to set the default value for the field to Null instead of 0.

Hi

Don't know if this issue is resolved but if not and you want to maintain your field as a text field try the following:;)

Open a new query and add the table hat has the primary key

In you first column drag in your primary key
in the second column enter ther following

PK:Format([PrimaryKeyFieldName],"00000")

This will prefix all the Pk's with leading zero's
View the datasheet and you will see the two columns, col 1 will show the PK as is, col 2 will show the PK with leading zero's.

Highlight the revised column and click copy
Next highlight the PK column and click Paste

This will replace all the primary keys with leading zero's.

Repeat this on all the tables that hold a reference back to the main table.
You will now have a completely updated table structure.

When you save new records to the tables remember to include the Format([FieldName],"00000") to ensure that each record has leading zero's.

Code Master:cool: