Trim String With Commas 2

[air1access]

I have values in a field that need to be trimmed based on a few criterias. I need to be able to basically "rebuild" the string to only include anything that starts with "GTWY*" or "Schd Date*" before AND after each comma. And if there is no "GTWY*" or "Schd Date*" anywhere in the value then just leave it Null... I sure hope this makes sense..!!! Below is a few examples of what I'm tryin' to explain. Any examples or suggestion would be much appreciated..!!! I am tryin' to work with the Len, Instr, Left Trim functions - but I'm just havin' a hard time with it...!!!

Thanks in advance..!!
air1access

-From this- GTWY has changed from RNO to SDF, Pr? has changed from N to Y, Op Type has changed from 2DA to WE
-to this- GTWY has changed from RNO to SDF

-From this- Pr? has changed from N to Y, Compliance Date has changed from NO ENTRY to 8/7/2008 10:00:00 AM, Op Type has changed from WE to 2DA, GTWY has changed from SDF to CID, Schd Date has changed from 8/9/2008 to 8/7/2008
-to this- GTWY has changed from SDF to CID, Schd Date has changed from 8/9/2008 to 8/7/2008

-From this- Schd Date has changed from 8/7/2008 to 8/8/2008, Op Type has changed from 2DA to WE
-To this- Schd Date has changed from 8/7/2008 to 8/8/2008

 

[JBinQLD]

G'day, would something like this help?

Function GetGTOrSchd(strToProcess as string) as string
dim intFoundAt as integer
dim intFoundComma as integer
dim strBitLeft as string

intFoundAt=instr(strToProcess,"GTWY")
if intfoundat>0 then 'got one
strBitLeft=mid(strToProcess,intfoundat)
intFoundComma=instr(","strBitLeft)
GetGTOrSchd=mid(StrToProcess,intFoundAt,len(strToProcess)-intFoundComma)
else
GetGTOrShd=""
end if

end function

Then call the function something like:

dim strResult as string
dim strFinal as string
do
strResult= GetGTOrSchd(YourlongStringToPass)
strFinal=strFinal & strResult
loop until stresult=""


Sorry I don't have access to check this out but if you've been playing already you'll see where i'm headed?

You could make a single parse function by using a Split function and then building your result from a loop through the resultant array checking for commas.

Many options but hopefully this helps.

 

[Golom]

An Alternative

Public Function NewString(TheString As String) As Variant
Dim SS() As String
Dim n    As Integer
SS = Split(TheString, ",")
NewString = Null
For n = 0 To UBound(SS)
   If Left(SS(n),4) = "GTWY" Or Left(SS(n),9) = "Schd Date" Then
      NewString = SS(n) & ","
   End If
Next
If Not ISNull(NewString) Then 
   NewString = Left(NewString, Len(NewString) - 1)
End If
End Function

 

[air1access]

Golom,

We are so close...!!!
Its rebuilding the string, but only on when the value starts with "GTWY" or "Schd Date"... I guessing this is due to the Left function... If "GTWY" and/or "Schd Date" is in the middle of the value, then it doesn't produce a new string and if "GTWY" AND "Schd Date" are both in the value, it doesn't produce a new string. In both situations, I will need the new string to include "GTWY" AND "Schd Date" seperated by a comma...
Any additional thoughts or suggestions...??
Thanks for the help..!!
air1access

 

[Golom]

OK. I misunderstood. Try this

Public Function NewString(TheString As String) As Variant
Dim SS() As String
Dim n    As Integer
SS = Split(TheString, ",")
NewString = Null
For n = 0 To UBound(SS)
   [red]If Instr(SS(n),"GTWY") > 0 Or InStr(SS(n),"Schd Date") > 0 Then[/red]
      NewString = SS(n) & ","
   End If
Next
If Not ISNull(NewString) Then 
   NewString = Left(NewString, Len(NewString) - 1)
End If
End Function

 

[air1access]

Golom,

Again.. We are so close...!!! The revision to your example rebuilds the new string regardless of where "GTWY" or "Schd Date" is within the value. But if "GTWY" AND "Schd Date" are both in the value, it only rebuilds the new string for the 1st occurance - not both. I need for both "GTWY" and "Schd Date" to be in the new string, seperated by a comma - if they are both in the original value/string...

Man you are so close...!! Any other suggestions...???
I'm tryin' to mess with what you provided in the mean time...
Thanks again...!!
air1access

 

[PHV]

Public Function NewString(TheString)
If IsNull(TheString) Then Exit Function
Dim SS() As String
Dim n As Integer
SS = Split(TheString, ",")
NewString = Null
For n = 0 To UBound(SS)
   If InStr(SS(n),"GTWY") > 0 Or InStr(SS(n),"Schd Date") > 0 Then
      NewString = NewString & "," & SS(n)
   End If
Next
If Not IsNull(NewString) Then 
   NewString = Mid(NewString, 2)
End If
End Function

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[air1access]

PHV,

Thank you very much...
That is exactly what I needed...!!!

air1access