Changing the length of a number

[CesarAustin]

Hope someone can help me, I have a table with one column with numbers from 1~999 I need to do some kind of update quarry or something to change the 1 and 2 digits to 3 digits… example: number 1,2,3 changed to 001, 002, 003 etc.. I have no idea how to do that without exporting the information from access to excel then manually changing the numbers in excel. Any help would be great.. Thanks all

 

[pritcham]

Hi

Try something along the lines of (SQL query):

Update myTable
Set myField = left("000",3-len(myField)) & MyField

Assuming you want the resulting field length to be 3 chars - if you want 4 then change the "000" to "0000" and the 3 to 4 and you should be set.

HTH
Martin

 

[CesarAustin]

Thank you pritcham… I’m not that good in make SQLs to work… my table is called “Systems Location”…. In that table I have two columns… the 1st column (name: “FLOOR”) has letters… and in the 2nd columns (name: “Jack Num”) I have the numbers.. 1~999… I want to change the 2nd column (or add a 3rd, what ever is easier) and add the 1st column to the 2nd column.. Example.. A001, B002, C015, D100.. etc…

 

[Norie]

Why not just format the number field as 000?

If you want to get A001, B002... etc you could use this expression in a query.

[FLOOR] & Format([Jack Num], "000")