remove leading zeros of unequal length

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

 

[MH]

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

 

[Guest]

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.

 

[Gary Walter]

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.

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] & "");

 

[Guest]

creating the module and then referring to it in a query worked like a charm

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.

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] & "");