Senin, 19 September 2011

Re: [MS_AccessPros] Re: Removing Leading Zeros

 

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]

__._,_.___
Recent Activity:
MARKETPLACE

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

.

__,_._,___

Tidak ada komentar:

Posting Komentar