Zeros Dropped (1 Viewer)

[nonrev1]

I have several thousand records already in a table. Each record should be identified by an 8 digit number but many began with a zero which was dropped so that those now show as a 7 digit number. Can I do an update query that will add a beginning 0 to all of the numbers that are 7 digits? If so, could someone assist me with the expression? The name of the field is [Branch]. TIA

 

[lewando_bria]

Hey nonrev...this is my first time replying to someone to try and help them so i might not be good at it...also, i'm a beginner at access so i would imagine my method is not too practical...but may get the job done....here we go...

I'm not sure what an update query is, but you could run a query to start a new row with all the new numbers....

first i set the value of the old number field ([Number]) to be text and put a mask on it so that it only allows digits 0-9 and only eight of them...in this way any new numbers you put in you wont have to worry about leaving out the zeros....

after that i ran a query to get a new number...the query field entry looked like this:

NewNum: GetZero([Number])

***calling a Public Function written in VBA

the function looked like this:


Public Function GetZero(nmbr As String) As String

Dim seven As String

seven = nmbr

If Len(seven) = 7 Then
seven = "0" + seven
End If

GetZero = seven

End Function

 

[lewando_bria]

Hey nonrev...this is my first time replying to someone to try and help them so i might not be good at it...also, i'm a beginner at access so i would imagine my method is not too practical...but may get the job done....here we go...

I'm not sure what an update query is, but you could run a query to start a new row with all the new numbers....

first i set the value of the old number field ([Number]) to be text and put a mask on it so that it only allows digits 0-9 and only eight of them...in this way any new numbers you put in you wont have to worry about leaving out the zeros....

after that i ran a query to get a new number...the query field entry looked like this:

NewNum: GetZero([Number])

***calling a Public Function written in VBA

the function looked like this:


Public Function GetZero(nmbr As String) As String

Dim seven As String

seven = nmbr

If Len(seven) = 7 Then
seven = "0" + seven
End If

GetZero = seven

End Function

this way the zeros are added to the old numbers...i'm not sure if this helped or not but i tried...also, i was under the assumption that these numbers wouldn't be used with math as in they were ID's of some sort so therefore they could be entered as text...aight lata

 

[antomack]

If the field is dropping zeros then the field is formatted as a number.

You have two choices in fixing the display to show 8 digits with leading zeros where required
1) Leave the field as a number but set the format of the field to '00000000'. This will mean the field will now display as 8 digits long with leadng zeros. Access though still stores the field without the leading zeros it's only the display that's changed.
2) Change the field to text. The leading zeros will still not show so run an update query with the following in the Update To
format([Branch],"00000000").
This will add leading zeros where required and Access will store the leading zeros. If there is any alpha text in the field the update will not work correctly for those fields.
If only one leading zero at most is required then the following will work. Set a field with an expression of Len([Branch]) with a criteria of 7. Use the following Update To under Branch
"0" & [branch]

 

[nonrev1]

Thanks antomak! Number 2 worked like a charm. It just took me a few minutes to realize Format had to be capitalized. I am brand new with these expressions.