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