Parse data then search for delimiter

[phoenix81]

Here is what I need to do:
I have 1 table that contains groups responsibilities, there is a field Code-group that the initiator uses to track the groups & their % of impact to the overall project hours. Sadly enough he enters the data as such (EX:
PROJECT1 GROUP1-25%, GROUP2-50%, GROUP3-25%.

The problem is that he has varied instances of groups listed in each record & wants me to be able to parse the data out regardless of how many groups are listed. I know how to parse data when there are a specific number of groups what I can't figure out is how to parse data regardless of how many groups is in the field.

Any help is totally appreciated.

Phoenix

 

[PeterF]

Try the split function, for your example it would look something like

Dim yourstring As String
Dim i As Integer
Dim StringtoParse As Variant
yourstring = "PROJECT1 GROUP1-25%, GROUP2-50%, GROUP3-25%."
StringtoParse = Split(yourstring, " ")

For i = 1 To UBound(StringtoParse)
Debug.Print StringtoParse(0) & " " & StringtoParse(i)
Next

You need to remove the . and , at the end of the strings if they don't mean anything

 

[phoenix81]

Thank you PeterF. I keep trying it and I can't get it to work. This is a little more advanced than I used to. Below is what I have in my database, please take a look at it and tell me what I have done wrong. Any help you can provide is greatly appreciated.

Thank you.

Public Function GetPart2(strvalue As String, intPart As Integer) As String

Dim strparts As String
Dim i As Integer
Dim stringtoparse As Variant
CodeGroup = "PROJECT1 GROUP1-25%, GROUP2-50%, GROUP3-25%"
stringtoparse = Split(CodeGroup, " ")

For i = 1 To UBound(stringtoparse)
Debug.Print stringtoparse(0) & " " & stringtoparse(i)
Next

End Function

 

[PeterF]

What do you want to do with it?
My sample gives only output to the debugging window of the VBA editor as yours does now. What is the table structure you want to enter the data?
To enter it in a table you can use a recordset or a append query which you can build in the function.
If YourTable has 3 fields (project, group, percent) replacing the debug.print statement with:

'note for this sample I used some reserved words do not use this in a database
'the fieldnames group and percent are for this reason in square braquets
"INSERT INTO YourTablename1 ( Project, [Group], [Percent] ) " & _
"SELECT '" & stringtoparse(0) & "" AS Project, " & _
"'"split(stringtoparse(i), "-")(0) & "' AS [Group], " & _
"'"split(stringtoparse(i), "-")(1) & "' AS [Percent];"

would enter a new record for every group in the original string.
To calculate with the strvalue you give as a parameter you need to replace the CodeGroup with StrValue, and where is the intpart used?
I would use a recordset because I think it's easier to maintain and easier to read, there are a lot of samples here on the site.

 

[DALeffler]

I believe you have been tasked with the impossible. You're being asked to catch (with code) every input error that anyone could possibly ever make.

Good luck wid dat.

Here's a starter proc that assumes a very specific user input string.

Sub GetPartn()

Dim ParsedElement As Variant
Dim intPart As Integer
Dim strPassed As String
Dim ProjectDesc As String

strPassed = "PROJECT1 GROUP1-15%, GROUP2-25%, GROUP3-35%, GROUP4-45%"
strPassed = strPassed & ","
intPart = 4

ProjectDesc = Mid(strPassed, 1, InStr(1, strPassed, " ") - 1)
strPassed = "," & Mid(strPassed, InStr(1, strPassed, " ") + 1)

ParsedElement = Split(strPassed, ",")

Debug.Print ProjectDesc & " " & Trim(ParsedElement(intPart))

End Sub

I think that's exactly how you would need begin to parse an input text string from a user provided that user supplied a perfect input string every time.

Me thinks you need to limit a user's input to your db via data normalization techniques and form GUIs. It's not that it can't be done: Buckets of money = Happy Users. But it can't be done simply.