Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Microsoft: Access Modules (VBA Coding) FAQ


Adding or Deleting Leading Zeros by JoanneM
Posted: 3 Sep 08

I noticed a few questions on other this and other forums  about Leading zeroes

Sorry if this is duplicated elsewhere and I am re inventing the wheel

'Pass your value as string.
'if data is numeric use Cstr('your number or  variable here')

'to convert to correct data type for the function

the Required length is the length of the string with leading zeros

Both accommodate Characters in the Values passed to them.
tested with values

Returns          0000d4569

? StripleadingZeros("00024c0569g00000000")
Returns                        24c0569g00000000


Function AddLeadingZeros(Dataval As String, RequiredLength As Integer) As String

Dim ReturnedString As String
Dim NumOfZerosToAdd As Integer

  NumOfZerosToAdd = RequiredLength - Len(Dataval)
  ReturnedString = String(NumOfZerosToAdd, "0") & Dataval
  AddLeadingZeros = ReturnedString

End Function


Function StripleadingZeros(Dataval As String) As String

Dim ReturnedString As String
Dim LastDigitNotZero As String
Dim PosLastdigitNotzero As Integer
Dim NumOfEndingZerosToAdd As Integer

'check for number Ending Zeros which will need to be

    LastDigitNotZero = Left(StrReverse(Trim(Replace(Dataval, "0", Chr$(32), 1))), 1)
    PosLastdigitNotzero = InStr(1, StrReverse(Dataval),  LastDigitNotZero)
    NumOfEndingZerosToAdd = PosLastdigitNotzero - 1

'Clear ALL Zeros & Trim away spaces

    Dataval = Trim(Replace(Dataval, "0", Chr$(32), 1))

'Replace spaces that should be zero & Replace Ending Zeros

    ReturnedString = Replace(Dataval, Chr$(32), "0", 1) &  String(NumOfEndingZerosToAdd, "0")

    StripleadingZeros = ReturnedString

End Function


Hope this proves usefull.

Back to Microsoft: Access Modules (VBA Coding) FAQ Index
Back to Microsoft: Access Modules (VBA Coding) Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close