Parse field based on placement of zero in field

[John1Chr]

Hi

I tried searching through this Microsoft access forum but couldn’t find exactly what I was looking for.

What I need to do is parse out a number based on whether or not the 6th through the 8th characters are zeros or not. This is a ten-digit number. However the last (10th digit place could be a letter.) In addition, the third character is always a zero and it needs to be removed every time. I want to remove the unnecessary zeros and insert a dash. I will use this field to link to another table. Right now, I am thinking that it might be easiest to create a new field in a new table with this newly parsed out field. See the example below:

In the field,
if this number is 1204500090 then give me 1245-90
If the number is 1204500890 then give me 1245-257
If the number is 1204507890 then give me 1245-7890
If the number is 1204567890 then give me 1245-67890

What would the code look like for this?

 

[Golom]

I don't get this one

If the number is 1204500890 then give me 1245-257

but assuming that it should have been

If the number is 1204500890 then give me 1245-[red]890[/red]

Then

NewValue = Left(TheNumber,2) & Mid(TheNumber,4,2) & "-" & _
           Val(Mid(TheNumber,6))

 

[John1Chr]

Oops! U r right! It should be 890.

 

[PWise]

not tested but as a start
var = field that you want to replace
replace(left(var,5),"0","")& val(mid(var,6,4)) & right(var,1)

 

[John1Chr]

Golom,

It worked except for the letter part. I apologize for not telling u that it had letters incorporated within the field.(There are 30,000 records to parse so initially, I did not catch it.) Here are examples of the problems.

field results
2300316X03 2303-16
7500600052PE 7506-52
8700316X03 8703-16
6703405308A 6734-5308
6703405309A 6734-5309
6703405462B 6734-5462
6703405598A 6734-5598
9808227V70 9882-27
9808227V84 9882-27

 

[Golom]

OK. And what would expect to be returned from

2300316X03 and 6703405308A ?

 

[John1Chr]

2303-16X03 and 6734-5308A

 

[ThomasLafferty]

Hi!
Not sure I understand the logic behind this:
If the number is 1204500890 then give me 1245-257
But I would suggest that you look at the MID function and the Replace function. Something like this:

Sub ParseString()
'*****VARIABLE DECLARATION*****
Dim strOrignal As String, strFirstPart As String
Dim strSecondPart As String, strResult As String

On Error GoTo Err_ParseString

Me.[!]myControl[/!].SetFocus

strOriginal = [!]myControl[/!].Text

          If Mid(strOriginal,ParseIt,1) = 0 Then
               strFirstPart = Left(strOriginal,2) & Mid(strOriginal,4,2)
          Else
               strFirstPart = Left(strOriginal,5)
          End If
          If Mid(strOriginal,6,3) = "000" Then
               strSecondPart = "-" & Right(strOriginal,2)

               ElseIf Mid(strOriginal,7,2) = "00" Then
                    strSecondPart = Mid(strOriginal,6,1)& "-" & Right(strOriginal,2)
               End If
               ElseIf Mid(strOriginal,8,1) = "0" Then
                    strSecondPart = Mid(strOriginal,6,2) & "-" & Right(strOriginal,2)
               End Else
          End If

strResult = strFirstPart & strSecondPart

Exit_ParseString:
     Exit Sub

Err_ParseString:
     Msgbox Err.Description
     Resume Exit_ParseString

End Sub

I wrote this on the fly, and it is NOT tested, so I am sure you will have to debug it a bit to get it to work. Items highlighted in [!]red[/!] above will have to be replaced with the name of your actual control. In particular, I'm not sure I have the ElseIf parts nested correctly, but I'm pressed for time.

Hope this helps...

Tom

Born once die twice; born twice die once.

 

[Golom]

Looks like you need a Function

Public Function ParsedString(RawValue As String) As String
Dim NS    As String
Dim EC    As String
Dim C     As String
Dim AddOn As Boolean
Dim n     As Integer

NS = Left(RawValue,2) & Mid(RawValue,4,2) & "-" 
EC = Mid(RawValue,6)
NS = NS & Val(EC)

If NOT IsNumeric(EC & "E0") Then
    For n = 1 To Len(EC)
        C = UCase(Mid(EC,n,1)) 
        If C >= "A" AND  C <= "Z" Then AddOn = True
        If AddOn Then NS = NS & C
    Next
End If

ParsedString = NS

End Function

 

[Golom]

One more question

should 230030X1234
[tt]
Produce 2303-0X1234

or 2303-X1234
[/tt]

?

If it's the second one then change the above code to

Public Function ParsedString(RawValue As String) As String
Dim NS    As String
Dim EC    As String
Dim C     As String
Dim AddOn As Boolean
Dim n     As Integer

NS = Left(RawValue,2) & Mid(RawValue,4,2) & "-" 
EC = Mid(RawValue,6)
If Val(EC) <> 0 Then NS = NS & Val(EC)

If NOT IsNumeric(EC & "E0") Then
    For n = 1 To Len(EC)
        C = UCase(Mid(EC,n,1)) 
        If C >= "A" AND  C <= "Z" Then AddOn = True
        If AddOn Then NS = NS & C
    Next
End If

ParsedString = NS

End Function

 

[John1Chr]

Thanks Golom!

I will give it a try. 230030X1234 should be 2303-X1234