Adding zeros at start of numbers 1

[Boccle]

Hi,

I have aproblem with a field in one of my tables.
The field is called LibraryNumber.

The problem is that when I put and A-Z sort on the field the numbers are not in the order I'd like them to be, for example:
1
12
13
134
20
22
255
3
35

As you can see they are not 'really' in numerical order. 20 really comes before 134 etc.

Each number really consists of 5 digits, e.g.:
1 should read 00001
134 should read 00134

How do I get the zeros in front???

Do i need some kind of imput mask?

Will the numbers then be in numerical order?

Anoher problem: The data already exists in the data base and there are about 20,000 records....so changing anything by hand really isn't an option!

Can anybody help???

TIA

 

[PaulBricker]

First, your numbers are actually text datatype. If you change the datatype to Number and set the Format property for the Field to
00000
then you should be able to get everything straightened out.

Paul

 

[OhBother30]

I'm sure it's not the most efficient way, but you can run an update query and update your field to the following.

right("00000"&[your Field Name],5)

OhBother

 

[Rick39]

Format([Your Field], "0000#&quot

 

[PaulBricker]

Any of these will work well for getting the 0's in place but only changing the text datatype from text to a number will actually allow you to sort it the way you need.

Paul

 

[JoyInOK]

If you want to leave your field as a text field (and I think you should, since you don't use it in calculations, you can do four update queries, testing the number of characters in the current field value and adding the appropriate # of zeros (four for fields of one character, three for fields of two characters, etc.).
If you're not too familiar with update queries, here's the gist of it.
Start in design veiw with a normal select query. Add the field you want to update to the query grid, and set the crieria row appropriately (in this case Len([NameOfFieldToUPdate]) = 1.
Switch to datasheet view to make sure your criteria pulled the right records.
Switch back to design view and on the toolbar, choose Query/UpdateQuery
A new row called Update To appears. Put the new value here
"0000" & [NameOfFieldToUpdate]
Click the ! button on the toolbar to excute the update. A message box will warn you of the number of changes being made.
Adjust the lenght criteria and number of zeros in the query to do the other three length possibilities.

 

[JoyInOK]

PaulBricker is correct that if you add the same number of zeros in front of every number (00001 and 000034) it still won't sort correctly, but if you put the appropriate number of zeroes so that every field is the same length (00001 and 00034), you can then sort in the correct order.

 

[MichaelRed]

JoyInOK

PLEASE READ SOME OF THE POSTING PRIOR TO YOURS IN THIS THREAD

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[JoyInOK]

MichaelRed, I did read them, although on a re-read I see that OhBother30 had a more efficient (all in one swoop -- very nice!) update criteria. Boccle didn't say if he/she was familiar with update queries, so I thought I'd provide some guidelines.
I think changing a datatype for a field is a rather radical solution for a sorting problem. All queries, reports and relationships that are based on the field would have to be adapted to reflect the change in order to prevent 'type mismatch' errors.

 

[MichaelRed]

My point exactly, OhBother30 has already posted a soloution. Other commentary is also useful and clarifies the situation beyond your contribution.

Changing the properties of the field doesn't actually place the values there, it only enforces additional entries to follow the [format | default value] property. Once the field values are formatted (properly) they do SORT correctly.

The Table:
IdNumAsText
00001
00134
00002
00126
00017
00075
00221

the query (SQL)[i
SELECT [tblIdNumAs Text].IdNumAsText
FROM [tblIdNumAs Text]
ORDER BY [tblIdNumAs Text].IdNumAsText
WITH OWNERACCESS OPTION;[/i]


The QueryResults
IdNumAsText
00001
00002
00017
00075
00126
00134
00221

Q.E.D.

So, changing hte MichaelRed
m.red@att.net

Searching for employment in all the wrong places