No Pun intended, but to get rid of leading Zero's, you can always Vote them out of office :)
terence
--- In MS_Access_Professionals@yahoogroups.com, mithomas48 <no_reply@...> wrote:
>
> 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
> > http://www.thatlldoit.com
> > Microsoft Office Access MVP
> > https://mvp.support.microsoft.com/profile/Bill.Mosca
> >
> >
> >
> > --- 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
> > >
> >
>
Tidak ada komentar:
Posting Komentar