remove leading zeros of unequal length

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a field of information that contains data as follows:

Example 1: 00000000000X03028
Example 2: 30028288

I need to remove the leading zeros in the first example and retain the
second example as it exits.
 
You could use an update query with the FORMAT function:

UPDATE MyTable
SET MyField = FORMAT([MyField],"0")

Butyou need to be careful where you have non-numeric data, the Format
function may try to transfer it to a date string.

MH
 
I can get you about half way there:

DropLeadingZeros: IIf(IsNumeric([YourField]) = True,
Val([YourField]),([YourField]))

If it wasn't for the alphabetical characters, it would be very easy to do.
 
lreecher said:
I have a field of information that contains data as follows:

Example 1: 00000000000X03028
Example 2: 30028288

I need to remove the leading zeros in the first example and retain the
second example as it exits.
Click to expand...

Here's 2 functions that have been provided
in previous posts (with their respective authors):

'//////////////////////////
Public Function StripZeros(strIn As String) As String
If Left(strIn, 1) = "0" And Len(strIn) > 1 Then
StripZeros = StripZeros(Mid(strIn, 2))
Else
StripZeros = strIn
End If
End Sub

' John W. Vinson[MVP]
'///////////////////////

Public Function LTrimZeros (ByVal pstrString As String) As String
'Removes Leading Zeros from a String

Do While Left(pstrString, 1) = "0"
pstrString = Mid(pstrString, 2)
Loop

LTrimZeros = pstrString
End Function

' Gordon Scott Bell
'///////////////////////

save one (or both) to a code module,
then, to use MH's example SQL:

UPDATE MyTable
SET MyField = LTrimZeros([MyField] & "");
 
creating the module and then referring to it in a query worked like a charm

Gary Walter said:
lreecher said:
I have a field of information that contains data as follows:

Example 1: 00000000000X03028
Example 2: 30028288

I need to remove the leading zeros in the first example and retain the
second example as it exits.
Click to expand...

Here's 2 functions that have been provided
in previous posts (with their respective authors):

'//////////////////////////
Public Function StripZeros(strIn As String) As String
If Left(strIn, 1) = "0" And Len(strIn) > 1 Then
StripZeros = StripZeros(Mid(strIn, 2))
Else
StripZeros = strIn
End If
End Sub

' John W. Vinson[MVP]
'///////////////////////

Public Function LTrimZeros (ByVal pstrString As String) As String
'Removes Leading Zeros from a String

Do While Left(pstrString, 1) = "0"
pstrString = Mid(pstrString, 2)
Loop

LTrimZeros = pstrString
End Function

' Gordon Scott Bell
'///////////////////////

save one (or both) to a code module,
then, to use MH's example SQL:

UPDATE MyTable
SET MyField = LTrimZeros([MyField] & "");
Click to expand...
 
Back
Top