How do you remove leading zeros?

[Guest]

I have a massive database that contains a table that has one column with
account numbers. However, I'm linking to another database and it's table with
the same information does not have leading zeros for that field.
So.. I need to modify the large database to remove all leading zeros to
properly link up and produce the correct output.
Please advise..

Thanks!

 

[Tom Lake]

I have a massive database that contains a table that has one column with
account numbers. However, I'm linking to another database and it's table
with
the same information does not have leading zeros for that field.
So.. I need to modify the large database to remove all leading zeros to
properly link up and produce the correct output.
Please advise..

Create an Update Query with one field (the account number field)
and in the Update To line use

Str(Val([accountnumber]))

Where accountnumber is the name of the field containing your account number
(!)

 

[limousin]

I've got a similar case but the account number has a mix of numbers and
alphanumerical values (000123 and L9887). I need to remove the leading zeroes
on the numerical values without damaging the alphanumerical ones.
Can anybody help?

Thanks
~~Marie

 

[John Spencer]

The only way I can see is a function for this case. Untested Air code
follows.

Public Function StripLeadingZeroes(strIn)
Dim i As Integer
If Len(Trim(strIn & vbNullString)) = 0 Then
StripLeadingZeroes = strIn
Else
For i = 1 To Len(LTrim(strIn))
If Mid(LTrim(strIn), i, 1) <> "0" Then
StripLeadingZeroes = Mid(LTrim(strIn), i)
Exit For
End If
Next i
End If
End Function

 

[limousin]

Thanks John, actually this one blanks out the whole field.
Could you help me with a function like that but using the convert to integer
(if numerical) and leave the value if the conversion fails?
Somebody sent me a function like that once but I lost it.

Thanks
~~Marie

 

[John Spencer]

The only way I can see the function blanking out the whole field is if the
field consists of just zeroes. The other possibility is that you entered
the letter O (Oh) instead of the number 0 (zero) in the comparison line.

If you only want to strip leading zeroes off if the entire field is numeric,
then just use

IIF(IsNumeric(SomeField),CStr(Val(Nz(SomeField,"0"))),SomeField)

This would only strip leading zeroes off value in the field if it could be
interpreted as a number, otherwise it returns the entire thing.

So "000A" would come back as "000A", but "001" would come back as "1"