Mike,
Nice. This assumes there are no internal spaces in the LOAN_NUMBER. If the LOAN_NUMBERs are only numerics with no spaces or letters, this should also work:
CStr(val(LOAN_NUMBER))
CStr(val("001230123")) = 1230123
My solution would truncate at the first occurance of a non-numeric value.
CStr(val("0012301s23")) =12301
CStr(val("0012301 23")) = 1230123
Duane Hookom
MS Access MVP
From: no_reply@yahoogroups.com
Bill, I came up with a solution using a combination of built-in functions - In case anyone's interested.
REPLACE(LTRIM(REPLACE(vNumber, '0', ' ')), ' ', '0')
1. Replaces all zeros with a space.
2. LTRIM removes the leading spaces (original leading zeros)
3. Replaces the remaining spaces back to the original zeros.
Thanks!
Mike
--- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca" <wrmosca@...> wrote:
>
> Mike
>
> You'll need a user-defined function (unless A.D. or John can give you a SQL solution).
>
> Function:
>
> Public Function RemoveLeadingZeros(varField As Variant) As Variant
> 'Purpose : Remove Leading zeros.
> 'DateTime : 9/19/2011 10:34
> 'Author : Bill Mosca
> 'Skip if null
> If IsNull(varField) Then Exit Function
>
> 'Remove leading zero.
> Do Until Left(varField, 1) <> 0
> varField = Mid(varField, 2)
> Loop
>
> RemoveLeadingZeros = varField
>
> End Function
>
>
> How to use it to update your table:
> Update MyTable
> Set MyField = RemoveLeadingZeros(MyField)
>
> Regards,
> Bill Mosca, Founder - MS_Access_Professionals
>
> --- In MS_Access_Professionals@yahoogroups.com, mithomas48 <no_reply@> wrote:
> >
> > What is the best way to remove leading zeros? For example, an account number could have any number of leading zeros. We need to find the first instance <> 0, then remove any leading zeros to the left of that number. We would like to do this within the INSERT INTO statement for the field name, "LOAN_NUMBER".
> >
> > Thanks in advance!
> > Mike
[Non-text portions of this message have been removed]
Tidak ada komentar:
Posting Komentar