INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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!

*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.

Jobs

Functions

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

?AddLeadingZeros("d4569",9)
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
'replaced

    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.
Jo

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

My Archive

Resources

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