Number formats, making standard 1

[kanne03180]

This seems like it should be easy, but I just can't seem to do it - I have two tables each with a field WO# unfortunately the fields are formatted differently. An example of the number is 02-005632-000-01. The number always has this many spaces but the number is sometimes entered as 2-5632-0-1, or if the 1 were not at the end it would be just be 2-5632 (which is the same as 02-005632-000-00).

How can I get these fields so that they match up? I'd like to be able to combine these tables into one and eliminate the duplicate entries...

Thanks, let me know if I can explain something a bit more...

-Kelleigh

 

[JeremyNYC]

So this took a bit of fiddling, but was relatively fun. You can use this in an update query or a sub that walks your recordset and updates the fields.

As a side note: this really should be stored in four fields and concatenated only for display. Dealing with these numbers is going to continue to be a painful experience if they're all in one field.

Hope this helps.

Jeremy

PS: Test and backup before using this.

Function ParseWO(strWO As String) As String
Dim intDash1 As Integer
Dim intDash2 As Integer
Dim intDash3 As Integer
Dim intTemp As Integer

intDash1 = InStr(strWO, "-&quot - 1
intTemp = (intDash1 + 2)
intDash2 = InStr(intTemp, strWO, "-&quot - intTemp
intTemp = intTemp + intDash2 + 1

'Dash1
ParseWO = Format(Left(strWO, intDash1), "00&quot & "-"
If intDash2 > 0 Then
'Dash2
ParseWO = ParseWO & Format(Mid(strWO, intDash1 + 2, intDash2), "000000&quot & "-"
intDash3 = InStr(intTemp, strWO, "-&quot - intTemp
If intDash3 > 0 Then
'Dash3 plus Dash4
ParseWO = ParseWO & Format(Mid(strWO, intDash1 + intDash2 + 3, intDash3), "000&quot _
& "-" & Format(Mid(strWO, intDash1 + intDash2 + intDash3 + 4), "00&quot
Else
'Dash3
ParseWO = ParseWO & Format(Mid(strWO, intDash1 + intDash2 + 3), "000&quot
End If
Else
'Dash2
ParseWO = ParseWO & Format(Mid(strWO, intDash1 + 2), "000000&quot
End If

End Function =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.