Parsing data from a field? 1

[valkyry]

Hi,
I can't figure out how to parse information from a field.

Here's an example:

FinishingHours
P7STF 4.50
STONE FINISH 2.00
STONE FINISH 3.00
STONE FINISH 4.00


They decided to add hours to the description. I need to parse out all the numeric values and then delete them once pulled and updated to another field I have reserved for the hours.


If it was consistent, I could use the Left, Right, Mid functions but they are not. The constent here is that all the numeric values in the description is at the end and that they are in fact decimals.

 

[valkyry]

Hi CMP,

This is what I am getting: see the "--", these are coming up Null.

Item FinishDesc FinishingHours
I4022 P1FF 0.50 0.50
I4022 FURN FINISH 11.50 --
I4022 FURN FINISH 17.50 --
I4022 FURN FINISH 24.50 --
I4023 SCAST 1.0 / FASS 1.0 1.0
I4023 SFIN 1.0 / FFIN 7.0 7.0
I4023 SFIN 1.5 / FFIN 14.0 14.0
I4023 SFIN 2.0 / FFIN 21.0 21.0

 

[CautionMP]

valkyry,
In the table is it: [tt]FURN FINISH 11.50_[/tt]?

If so try using [tt]GetNumeric(Trim(RoutingNumber))[/tt] or you could add the [tt]Trim()[/tt] to [tt]GetNumeric()[/tt].

Function GetNumeric(SourceString As String) As String
Const NumericCharacters As String = "1234567890."
Dim lngCharacter As Long
[b]Dim strSource As String[/b]
Dim strCharacter As String
[b]strSource = Trim(SourceString)[/b]
lngCharacter = InStr([b]strSource[/b], "FF")
If lngCharacter = 0 Then
  lngCharacter = InStrRev([b]strSource[/b], " ") + 1
Else
  lngCharacter = InStr(lngCharacter, [b]strSource[/b], " ") + 1
End If
For lngCharacter = lngCharacter To Len([b]strSource[/b])
  strCharacter = Mid([b]strSource[/b], lngCharacter, 1)
  If InStr(NumericCharacters, strCharacter) <> 0 Then
    GetNumeric = GetNumeric & strCharacter
  End If
Next lngCharacter
End Function

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[valkyry]

that was it.

the funny thing is, if you saw the SQL View, I had Trim() in the query all ready and then the GetNumeric().

well, having the Trim in the function first seems to have fixed it!

Thanks for seeing that. I'm sure I would not have figured that out!

I would be spinning on my query and would have been going crazy because it didn't make sense!!

 

[Remou]

valkyry
I think you owe CautionMP a very, very big purple star.