Adding Zeros

  • Thread starter Thread starter NotGood@All
  • Start date Start date
N

NotGood@All

I have a table that has a field named PersonID, it’s a text field with all of
the text being digits. Some of the digits are, 2345, 21345, 982345, or
0012345. I would like to make all the characters 7 digits in length. If the
PersonID only has 4 characters I want to add 3 zeros, 5 chars, 2 zeros, 6
chars, 1 zero
 
Maybe make a public function, then run an query to update the value of all
PersonID to PersonIDNew(PersonID). The code below would take an existing
PersonID and begin adding zeros to the beginning until the total length is 7
characters, then return the result.

Public Function PersonIDNew (PersonID As String) As String
Dim strLen as Integer
PersonIDNew = PersonID
strLen = Len(PersonIDNew)
Do While strLen <7
PersonIDNew= "0" & PersonIDNew
strLen = Len(PersonIDNew)
Loop
End Function
 
Do you actually have to STORE seven digits (including zeros), or would it be
enough to DISPLAY seven digits?

What are you intending to use the seven-digit version for? If you are
trying to match it to some other data that has seven, including zeros, that
makes more sense.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Here are two expressions that should work for you.

Format(PersonId,"0000000")

Or

Right("0000000" & PersonID,7)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
I have a table that has a field named PersonID, it’s a text field with all of
the text being digits. Some of the digits are, 2345, 21345, 982345, or
0012345. I would like to make all the characters 7 digits in length. If the
PersonID only has 4 characters I want to add 3 zeros, 5 chars, 2 zeros, 6
chars, 1 zero
Click to expand...

Just run an Update query:

UPDATE yourtable
SET PersonID = Right("0000000" & [PersonID], 7)
WHERE Len([PersonID]) < 7;

Note that if there are relationships defined to other tables using PersonID
you will need to have referential integrity set, and the Cascade Updates box
checked in the relationships window. You might want to set cascade updates on
temporarily while you're doing this update and turn it off afterward.
 
Thanks to all. Yes I'm trying to match these records up some images that sit
outside of access. The update query worked very nicely. My next question is
does anyone remember how to rename files in a windows directory? The files
in the directory have the first 7 digits in the access table but have some
stuff after them; 0012345;Jan-Feb-87-3.tif. I need to remove everything
after the seventh character and put the .tif back on. I tried Ren *.*
???????.tif but that did not work
John W. Vinson said:
I have a table that has a field named PersonID, it’s a text field with all of
the text being digits. Some of the digits are, 2345, 21345, 982345, or
0012345. I would like to make all the characters 7 digits in length. If the
PersonID only has 4 characters I want to add 3 zeros, 5 chars, 2 zeros, 6
chars, 1 zero
Click to expand...

Just run an Update query:

UPDATE yourtable
SET PersonID = Right("0000000" & [PersonID], 7)
WHERE Len([PersonID]) < 7;

Note that if there are relationships defined to other tables using PersonID
you will need to have referential integrity set, and the Cascade Updates box
checked in the relationships window. You might want to set cascade updates on
temporarily while you're doing this update and turn it off afterward.
Click to expand...
 
Thanks to all. Yes I'm trying to match these records up some images that sit
outside of access. The update query worked very nicely. My next question is
does anyone remember how to rename files in a windows directory? The files
in the directory have the first 7 digits in the access table but have some
stuff after them; 0012345;Jan-Feb-87-3.tif. I need to remove everything
after the seventh character and put the .tif back on. I tried Ren *.*
???????.tif but that did not work
Click to expand...

It's too late for me to be thinking clearly, but you'll need to use a loop
with multiple calls to the Dir function, and then use the Name function to
rename the files:

Dim strFilename As String, strNewname As String
strFilename = Dir "C:\yaddayadda\*.tif"
Do While strFilename <> ""
strNewname = Mid(InStrRev(strFilename, "\")) ' strip off path
strNewname = Left(strFilename, Len(strNewname ) - 4 ' strip off ".tif"
If Len(strNewname ) > 7 Then
strNewname = Left(strNewname , 7) & ".tif"
Name strFilename strNewname
End If
Loop

UNTESTED AIR CODE - try it in a scratch folder first!!!
 
Brian said:
Maybe make a public function, then run an query to update the value of all
PersonID to PersonIDNew(PersonID). The code below would take an existing
PersonID and begin adding zeros to the beginning until the total length is 7
characters, then return the result.

Public Function PersonIDNew (PersonID As String) As String
Dim strLen as Integer
PersonIDNew = PersonID
strLen = Len(PersonIDNew)
Do While strLen <7
PersonIDNew= "0" & PersonIDNew
strLen = Len(PersonIDNew)
Loop
End Function
Click to expand...

Or just use

PersonIDNew = Right("0000000" & PersonID, 7)

or just display it with the extra zeroes in the text box Format.

Tom Lake
 
Back
Top