How Do I reload a table using a format in a Text field. 1

[spining123]

I have a field called Employee Number.
It is defined in the table as a text field.
I would like to pad the table with leading zeros.
In the table design field Employee Number; I enter "000000" for the format.
How do I reload this table to display the leading zeros in the table.
Example: Employee Number in the table now.
10
100
20

Would like for the Employee Number to look like...
000010
000100
000020
so I can sort on this field.

 

[SteveR77]

You could do a CInt(EmployeeNumber) then sort on this field in a query.


The issue is the data type of text will be ASCII sorted.

The only other option would be to leave the format as it is and change the field so it is a number data type and then make it an index so it always stays sorted.

HTH,

Steve

 

[Rolliee]

spin,

if num = 10 then

format(num,"0000000000&quot = 00000010

Okay?

rollie@bwsys.net

 

[MichaelRed]

You can use Rolliee's crytpic response to upddate the field in an (guess what?) an Update query:

Format(EmployeeNumber, String(5, "0&quot)


For more general use in generating fixed length strings with a specific "Pad":

Public Function basPad(PadVal As Variant, _
                       PadChr As String, _
                       RtnLen As Integer, _
                       PadSide As String) As String

    'Michael Red 1/10/04 for Tek-Tips thread701-746259 (vanessa03)
    'Sample Usage:
    '? basPad(9874564, " ", 10, "right") & "01234)"
    '9874564   01234

    Dim MyPad As String

    MyPad = String(RtnLen, PadChr)

    Select Case UCase(Left(PadSide, 1))
        Case Is = "L"
            basPad = Right(MyPad & PadVal, RtnLen)
            
        Case Is = "R"
            basPad = Left(PadVal & MyPad, RtnLen)

    End Select

End Function

Which, for your specific request would be used in an UpdateQuery as:


basPad(EmployeeNumber, "0", 5, "L&quot

Of Course, to simply get the "Sorting" part to work you don't actually need to use the "Zero" character, as any one (even a Space) will work just as well:

e.g.

A "concocted" table:
EmployeeNumber

100
5
8
22
14
36
12
2

A query to pad with Spaces (in situ):
UPDATE tblEmpNum SET tblEmpNum.EmployeeNumber = basPad([EmployeeNumber]," ",5,"L&quot
WITH OWNERACCESS OPTION;

The Table, afgter the "Update" query shown above:
EmployeeNumber

  100
    5
    8
   22
   14
   36
   12
    2

The simple Sort Query on the "Re-Formatted" Table:
SELECT tblEmpNum.EmployeeNumber
FROM tblEmpNum
ORDER BY tblEmpNum.EmployeeNumber
WITH OWNERACCESS OPTION;

and the Query Results
EmployeeNumber:

    2
    5
    8
   12
   14
   22
   36
  100

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Rolliee]

nice one, Michael - here's a star

 

[MichaelRed]

Then, again, the above is un-necessarily complicated, as you can just leave the [EmployeeNumber] field as is (as far as sorting is concerned) by simply combining the above:

the combo query
SELECT basPad([EmpNum]," ",5,"L&quot AS EmplNum
FROM tblEmplNum
ORDER BY basPad([EmpNum]," ",5,"L&quot
WITH OWNERACCESS OPTION;

I did change the names around a bit, but the entire process is accomplished w/o any special treatment of the original values in the table.

All of which only leads to other issues, such as how are you making sure that duplicate values are not entered for your field?






MichaelRed
m.red@att.net

Searching for employment in all the wrong places