modifying data in a field

[jbmusic]

Hello,
For more than ten years I have been using a database I created of my High
School Band's Music Library. Each record contains an acquisition number,
title, composer, arranger, publisher, and date of last performance as
individual fields. The Concert Band music uses a acquisition numbering
system: C001, C002, etc., everything was going great until last year when I
entered record, C1000. When I sort the records by acquisition number, Access
2003, places C1000 right after C100, NOT after C999!
Is there anyway, short of individually removing all 1026 "C's", to make
Access sort the records into true numerical order (C998, C999, C1000, C1001)?
Or, how do I remove all 1026 "C's" in one-fell-swoop?
Thank You for your time and cooperation with this question.

 

[Dennis]

Take a backup copy of your table before you do this.
You can either update all your data to remove the 'C' or update the data to
insert an extra '0' after the 'C'

Create a query and add just your acquisition field. Change the query type to
an update query and then in the 'Update to' row of your field put either
Mid([acquisition number],2)
or
"C0" & Mid([acquisition number],2)

After you have run the query, don't forget to manually edit the C1000
acquistion number back to C1000 as the update query will have modified it.

 

[Jerry Whittle]

In a query put the following in the Field with the proper name. Sort on this
field. You can remove the checkmark so that it doesn't show. If you are doing
the sort in a form or report, you'll need to have it there, but it can be
hidden or minimized.

SortOrder: CLng(Mid([TheFieldName],2))