Senin, 19 September 2011

[MS_AccessPros] Re: Removing Leading Zeros

 

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

__._,_.___
Recent Activity:
MARKETPLACE
A bad score is 596. A good idea is checking yours at freecreditscore.com.

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.

.

__,_._,___

Tidak ada komentar:

Posting Komentar