Leading Zero Problem

[Plasma101]

Guys I have a Linked table which I need to query, in the query I need to strip out all the leading zeros on part numbers that do not have an alpha Character i.e.

Before:

000000000019759603
000000000009759603
000000000009759603
0108A2
0108B3
0108C3

Result wanted:

19759603
9759603
9759603
0108A2
0108B3
0108C3

So my question is how do I get the query to give me the results I require.
Any advice would be appreciated

Thanks

Chris

 

[ShiftyJaffa]

Try creating a public function like this below and calling it from your query:-

Public Function StripZeros(MyData As String)
Dim Count As Integer

'If blank then exit
If Len(Nz(MyData)) = 0 Then StripZeros = MyData: Exit Function

'Scan it for characters and if found then exit
For Count = 1 To Len(MyData)
If Asc(Mid(MyData, Count, 1)) > 57 Then StripZeros = MyData: Exit Function
Next Count

'Strip leading Zero's
Do
If Left(MyData, 1) = "0" Then MyData = Mid(MyData, 2)
If Len(Nz(MyData)) = 0 Then Exit Do
If Left(MyData, 1) <> "0" Then Exit Do
Loop
StripZeros = MyData
End Function

After this your query would be something like

SELECT StripZeros(nz(PartNumber)) FROM tblMyTable

There maybe an easier way than this in newer versions of access, but I only work in access 97.

Hope that helps

Paul

 

[Plasma101]

Thanks Paul I'll give it a try
Cheers

Chris

 

[Jon K]

Alternatively, you can use this expression in a query to get the results:-

NewField: IIf(IsNumeric([FieldName]), Val([FieldName]), [FieldName])
.

 

[Plasma101]

Jon
Thats perfect thanks a lot

Cheers

Chris

 

[ShiftyJaffa]

You live and learn , handy to know that.

Cheers Jon

 

[Arian]

Alternatively, you can use this expression in a query to get the results:-

NewField: IIf(IsNumeric([FieldName]), Val([FieldName]), [FieldName])
.

I'm sorry.. how would I be able to use this?

I'm a newb at Access and SQL. What I want to do is, use a query to go through a certain column, and remove the leading zeros where the first letter is not a character.

should I be doing something like this?

UPDATE CBF
SET ID = IIf(IsNumeric([ID]), Val([ID]), [ID])

ID is of type string, since in some cases, there is a character in ID.