Parse String

[Kevin_S]

Parse String Help Needed!

Hi All - in a bit of a bind on this one so I could use a bit of help here!

I have an ID field in a db that is a combination of numeric and text put together. An example of what this looks like is this:

Letter P or L "-" Number "-" Number so... this ID could range from this:

P-1-1

to...

P-1836-7354

Now - what I need to do is seperate this into sections so that I can do some searching on the different sections but I'm at a loss on how to do this!?!? I am pretty comfortable with the Mid(),Left(), and Right() functions but those require knowing the set number of spaces to move and select data from and as you can see from the example above the length of the ID can be anywhere from 5 characters to 11...? If I could break out the string by sections before and after the hyphens then I would be all set but.... again I dont know how...

Any/All help on this is really appreciated!

Thanks,
Kev

 

[Dreamboat]

Hi, Kev. I copied (okay, STOLE!) this code from somewhere else (I don't do code, but sure know how to find it!). Apparently, this code finds the characters between less than and greater than signs and deletes it (html??). You'll just need to change the delimiter to a hyphen, right? And then, not delete it...

Give this code example a try. I have used a field called Comments. The update only occurs on records with both a "<" and ">" in the field. If there are more than one field or more than one instance in each field then multiple executions would have to be made. Run it for one field until there are no more selected text, then change the field name to another field and run until completed. I have written this to remove the "<" and ">" with each update.

UPDATE tblYourTable SET tblYourTable.Comments = Mid$([tblYourTable]![Comments],1,InStr(1,[tblYourTable]![Comments],"<")-1) & Mid$([tblYourTable]![Comments],InStr(1,[tblYourTable]![Comments],">")+1)
WHERE (((InStr(1,[tblYourTable]![Comments],"<"))>0) AND ((InStr(InStr(1,[tblYourTable]![Comments],"<")+1,[tblYourTable]![Comments],">"))>0));

 

[Kevin_S]

Hey Dreamboat thanks for the help - Unfortunitley I'm not really sure how to apply this to my needs. I am trying to do this in a DMax function with 2 criteria - here is the function to date:

intGPS = Nz(DMax("[GPSSURVEY_ID]", "dbo_PARKINVT_INVENTORY", "[UNITID]=" & Me.UNITID And Left(GPSSURVEY_ID, 1) = Me.cboGPSF) + 1, Me.cboGPSF & "-" & Me.UNITID & "-" & "1")

Basically what I need to do (I've kind of switched gears here a little so bear with me ) is find the next highest value in the GPSSURVEY_IN field (its text) where the UNITID in the same table = the Unit value on the form and the left most character of the GPSSURVEYID field = the combo selection on the form.... THEN - I take the value and add 1 or, if nothing is found, create a new value....

This make any sense and do you have any thoughts?

Thanks!
Kev

 

[Kodo]

Use split.

MyNewVar=split(id,"-")

this will create an array with the elements P,1,1 in positions
0,1,2. So you'd retrieve the values like so:

MyNewVar=split(id,"-")

For i=0 to Ubound(MyNewVar)
debug.print MyNewVar(i)
next

 

[Kevin_S]

Use split.

MyNewVar=split(id,"-")

this will create an array with the elements P,1,1 in positions
0,1,2. So you'd retrieve the values like so:

MyNewVar=split(id,"-")

For i=0 to Ubound(MyNewVar)
debug.print MyNewVar(i)
next

Hey Kodo - thanks for the input as this is the zero hour for this project and stress level is high!

How would I implement what you provided in my DMax function above????

Sorry for the added request but I'm in panic mode and I'm not thinking very clearly right now!!!

THANKS MUCH!
Kev

 

[Kodo]

Got to be honest. I don't use D() functions at all. If you want to reference a particular part of the array and assign it to a variable so you can put it into your Dmax function, you would do this.

LetterVal=MyNewVar(0) 'THIS=P
FirstNumVal=MyNewVar(1) 'THIS=1
SecondNumVal=MyNewVar(2) 'THIS=1