Mike,
That was a nice solution. Well done!
However, as pointed out by Duane, it won't cater to existing internal spaces (if any) in Loan_Number, while CStr(Val()) based approach would truncate the result at first occurrence of a non-numeric value within the source string.
Sample append query Q_App_Loans as given below, is expected to be capable of satisfactorily handling various points outlined above. It appends Loan_Number from table T_Temp into table T_Loans, after duly removing the leading zeros.
Q_App_Loans (Sample Append Query)
============================
INSERT INTO T_Loans ( Loan_Number )
SELECT Mid([Loan_Number],Len(Val("1" & [Loan_Number]) & "")-Len(Val([Loan_Number]) & "")) AS Expr1
FROM T_Temp;
============================
In the sample query, length of string made up of leading zeros is used for carrying out its removal. You might like to try it out on a larger data set and confirm the outcome.
Best wishes,
A.D. Tejpal
------------
----- Original Message -----
From: Duane Hookom
To: Access Professionals Yahoo Group
Sent: Tuesday, September 20, 2011 00:21
Subject: RE: [MS_AccessPros] Re: Removing Leading Zeros
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]
Senin, 19 September 2011
Re: [MS_AccessPros] Re: Removing Leading Zeros
__._,_.___
MARKETPLACE
.
__,_._,___
Langganan:
Posting Komentar (Atom)
Tidak ada komentar:
Posting Komentar