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.

 

[CautionMP]

valkyry,
If the string you want to parse is not too long you could use something like this:

Function GetNumeric(SourceString As String) As String
Const NumericCharacters As String = "1234567890."
Dim lngCharacter As Long
Dim strCharacter As String
For lngCharacter = 1 To Len(SourceString)
  strCharacter = Mid(SourceString, lngCharacter, 1)
  If InStr(NumericCharacters, strCharacter) <> 0 Then
    GetNumeric = GetNumeric & strCharacter
  End If
Next lngCharacter
End Function

It gives me output like this:
[tt]GetNumeric("STONE FINISH 2.00")
= 2.00[/tt]

Hope this helps,
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]

It's kind of working.

Don't know why it's doing the following. THe first one for each item looks like it's adding a 7 in from of the numeric values.

Item FinishDesc FinishingHours
FT48D P7STF 4.50 74.50
FT48D STONE FINISH 2.00 2.00
FT48D STONE FINISH 3.00 3.00
FT48D STONE FINISH 4.00 4.00
FT51D P7STF 4.50 74.50
FT51D STONE FINISH 2.00 2.00
FT51D STONE FINISH 3.00 3.00
FT51D STONE FINISH 4.00 4.00
FT54D P7STF 4.50 74.50
FT54D STONE FINISH 4.25 4.25
FT54D STONE FINISH 2.25 2.25
FT54D STONE FINISH 3.25 3.25

 

[CautionMP]

valkyry,
It's doing what it's designed to do, I just missed the numeric values in the description.
[tt]Item FinishDesc FinishingHours
FT48D P7STF 4.50 74.50[/tt]

A work around is to start at the first space " "?

...
Dim strCharacter As String
For lngCharacter = InStr(SourceString, " ") To Len(SourceString)
  strCharacter = Mid(SourceString, lngCharacter, 1)
...

CMP

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

 

[Remou]

How about InstrRev?

[tt]a = "STONE FINISH 4.00"
a = "P7STF 4.50"

Debug.Print Mid(a, InStrRev(a, " ") + 1)
Debug.Print Mid(a, 1, InStrRev(a, " "))[/tt]

 

[valkyry]

Thanks CautionMP,
it seems to be working!

 

[CautionMP]

valkyry,
You might incorporate Remou's suggestion since it should make the code run faster (less characters to iterate).

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]

ah, will try.

thanks all!

 

[valkyry]

ok, it's even a little more complicated.
They also have two options in the field.

SFIN 5.0/FFIN 2.25 and of course inconsistently as well.
SFIN 05.0 / FFIN 2.25 or SFIN 05 /FFIN 2.25.

At least I found out that they just want the numeric value for the one that starts with FF*.

So, how do I incorporate the above with the code provided?

 

[valkyry]

Here's the actual:

Item Descritption FinishingHours
I12003 P7STF 2.00 2.00
I12003 STONE FINISH 4.25 4.25
I12003 STONE FINISH 2.75 2.75
I12003 STONE FINISH 2.00 2.00
I12005 P1FF 2.00 2.00
I12005 FURN FINISH 4.00 4.00
I12006-L P7STF 3.50 3.50
I12006-L SFIN .50 / FFIN 2.25 .502.25
I12006-R P7STF 3.50 3.50
I12006-R SFIN 0.50 /FFIN 2.00 0.502.00


How can I add or change the code to say for the ones with */* to only use the numeric values after FFIN descriptions?

Also, the numeric values from the descriptions that starts with a P#* to be added to the other values for the same Item?

It seems that this row of data per Item is an additional cost to the other values. So for example:
Item Descritption FinishingHours
I12003 P7STF 2.00 2.00
I12003 STONE FINISH 4.25 4.25
I12003 STONE FINISH 2.75 2.75
I12003 STONE FINISH 2.00 2.00

The 2.00 from the P#* (P7STF) needs to be added to the 4.25 so the FinishingHours will be 4.25+2=6.25.
The next one would be 4.75 instead of just 2.75 and the last one should be 4.00 instead of 2.00.

Did that make sense?


This has grown as each bit is determined some other variable seems to be coming up. And of course they can't remember all of the variations until it does come up to tell me about them.

 

[CautionMP]

valkyry,
This is the third revision so I restated the entire function.

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

Here are my results:
[tt]SFIN 05.0 / FFIN 2.25 2.25
SFIN 05 /FFIN 2.25 2.25
P7STF 4.50 4.50
STONE FINISH 2.00 2.00[/tt]

As far as doing the addition, once you have the values placed in a new field you could use a query to Sum [tt]FinishingHours[/tt] when you Group By [tt]Item[/tt].

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]

thanks, not sure what's going on but I am only getting results with the FF and nothing else.

Item FinishDesc FinishingHours
I12005 P1FF 2.00 2.00
I12006-L SFIN .50 / FFIN 2.25 2.25
I12006-R SFIN 0.50 /FFIN 2.00 2.00
I13027 P1FF 2.00 2.00
I13028 P1FF 1.50 1.50

 

[ProgramError]

Try working backwards until you find a space.
Don't forget to build the time in reves order if you do it this way. ie.
(not tested)

Dim Position as long
Dim strTime as String

position = Len(TheString)
While mid(TheString, Position, 1)<> " "
strTime = right(TheString, Position, 1) & strTime
Wend

This assumes that there is no space at the endof the string to begin with.
Just another way of thinking...


Ian Mayor (UK)
Program Error
Programming is 1% coding, 50% error checking and 49% sweat as your application bombs out in front of the client.

 

[ProgramError]

opps forgot the position adjustment

Dim Position as long
Dim strTime as String

position = Len(TheString)
While mid(TheString, lngPosition, 1)<> " " and lngPosition >1
strTime = right(TheString, lngPosition, 1) & strTime
If lgnPosition > 1 then lngPosition = lngPosition - 1
Wend


Ian Mayor (UK)
Program Error
Programming is 1% coding, 50% error checking and 49% sweat as your application bombs out in front of the client.

 

[valkyry]

thanks

i got a compile error at the Right

Wrong number of arguments or invalid property ...

 

[CautionMP]

valkyry,
These are all variations of the same theme, you can keep trying different itterations of the same function, but...

I suspect the issue you had with disapearing data (post 18 Aug 06 0:57) is not related to the function, but the application of the function.

[ul][li] Are you using the function in a query?[/li]
[li]If so are you trying to Group By in that query?[/li]
[li]If you are what did you do to get the [tt]FinishDesc[/tt] to show?[/li][/ul]

It's probably something in the query that's filtering the records and not the function(s).


Post your query source (Select SQL view from the QBE pane).

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]

Well, the query didn't change, just the function at this point.

There is not a Group By in the query.

here's the SQL view of the query:
SELECT Trim([BillNumber]) AS Item, Trim([OptionCode]) AS FinishLevel, GetNumeric([RoutingNumber]) AS FinishingHours, IIf(Trim([BillDescription1]) Like "*/*",Mid(Trim([BillDescription1]),9,9),Trim([BillDescription1])) AS ItemDesc, Trim([BM1_BillMaterialsHeader].[RoutingNumber]) AS FinishDesc, BM1_BillMaterialsHeader.OptionCategory
FROM BM1_BillMaterialsHeader
WHERE (((Trim([BillNumber])) Not Like "S*" And (Trim([BillNumber])) Not Like "P*" And (Trim([BillNumber])) Not Like "FS*") AND ((GetNumeric([RoutingNumber]))<>"") AND ((BM1_BillMaterialsHeader.OptionCategory)="1" Or (BM1_BillMaterialsHeader.OptionCategory)="") AND ((Trim([RoutingNumber]))<>""))
ORDER BY Trim([BillNumber]), BM1_BillMaterialsHeader.OptionCategory;

 

[lespaul]

i got a compile error at the Right

Wrong number of arguments or invalid property ...

did you search Access help for the RIGHT function and try to figure out what was wrong with the line of code? (if you select the 'Right' text and press F1, Access will open help at that function). The error message you were given tells you exactly why it's not working - wrong number of arguments.

The RIGHT function only has two arguments, what to search and how many to return. The RIGHT function listed above has three arguments. You need to correct that.

You have been given alot of help on this issue (the code was basically written for you), it is not unreasonable for us to expect you to try to solve basic errors like this yourself.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[valkyry]

lespaul,
I need help, hence why I posted here. I'm not a programmer and not good with code (as you can see).

I can get by with the REAL basic stuff, but beyond that I really don't know much. Again, reason why I posted.

I certainly appreciate the help and yes, sometimes it's the code in it's entirety to perfection, simply because I can't pick and choose what's working or not.

Now for the error on the Right function, I don't get, the function appears to be written correctly as I do understand the standard and BASIC functions of the Left, Mid, and Right. But that's my limit. :-(


If anyone is willing to continue on helping me, PLEASE, I still need it!

 

[CautionMP]

valkyry,
I have tested the restated funtion posted 18 Aug 06 0:50 and it seems to be working so I suspect that's not the problem.

Try a simple query to test the function.
[tt]SELECT BillNumber, RoutingNumber, GetNumeric(RoutingNumber) FROM BM1_BillMaterialsHeader;[/tt]

If the records without "FF" show up then you know it's not the function and you probably need to double checking your filters.

CMP

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