Pull value from a string of text 1

[ehsguy77]

I have a text field in a table that contains a 3 part text value - one part of the value is contained in parenthesis. I'd like to extract the part of the value in parenthesis and use it to update another field within the recordset. The field is called LEIcontainer#, and the values look like this 04(4-30)025. I'd like to populate field LEIwasteCode with value 4-30.

Any ideas? I've never had to do anything like this before.

 

[PHV]

Something like this ?
UPDATE theTable
SET LEIwasteCode = Mid(Left([LEIcontainer#],InStr([LEIcontainer#],")")-1),1+InStr([LEIcontainer#],"("))
WHERE [LEIcontainer#] Like "*(*)*";

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[sha76]

LEIwasteCode:Mid(([TableName]![LEIcontainer#],InStr(([TableName]![LEIcontainer#],"(")+1,InStr(([TableName]![LEIcontainer#],")")-InStr(([TableName]![LEIcontainer#],"(")-1)

Will give you the value within the brackets, although you could simplify it if any part was a fixed length.

(In English; take the mid-section, starting one place after the opening bracket, length equal to position of 2nd bracket - position of 1st bracket - 1)

 

[ehsguy77]

Thanks for the replies...but I don't know where to put these - expressions? Or is it code? Should this be in the update field in an update query, or an event code, or what?

Please forgive my ignorance.

 

[PHV]

My code is a full SQL of an Update query.
Backup your database.
Open a query window, display the SQL pane and then paste the posted code.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[sha76]

Sorry, should have said, it's for an update query. Though whichever way you do it I'd go with PHV's Mid expression, as it's tidier than mine! (also I seem to have ended up with too many opening brackets along the way, all the double ones should be single, I'd best go get more coffee!)

 

[ehsguy77]

Thanks to both of you. The WHERE part of the statement was throwing me off - it works fine without it.

 

[PHV]

And with this clause ?
WHERE [LEIcontainer#] Like '%(%)%';

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ehsguy77]

Where clause still doesn't work. Works fine without, though. All of my LEIcontainer#'s have that format, though.

 

[CosmoKramer]

Just a thought about the idea of storing the new value as a separate field...

Will the value stored in LEIcontainer ever change for a record? Or, will you be adding new records to the table, necessitating future update query runs? If so, you may want to consider using PHV's statement in a user-defined field in a query, to help keep it more dynamic......



Hoc nomen meum verum non est.

 

[ehsguy77]

Actually, the LEIcontainer# will be a static record, so the LEIwasteCode field is static as well. Thanks for the suggestion - may be helpful in the future.