extracting data from field

[focosi]

I have a field in a table containing data like these :



"A21(1500),B27(500),C18(1000),DP15(2000)"
"A32(2000),B27(1000),C19(2000),DR20(1000)"

I also have a query by form that finds all records containing, e.g., "B27" by setting the criterion to :

Like "*" & [Forms]![Form_name]![Search_field_name] & "*"

Is there any way to output in a query column the value between parentheses (i.e., for B27 :
500
1000

??
I guess I have to use a combination of InstrRev and Right functions, but can't find the right way to combine them...

[Robeen]

I just tried this in a new field in a query:
InParens: Mid([REASON],InStr([REASON],"(")+1,(InStr([REASON],")")-1)-InStr([REASON],"("))

The field 'InParens' extracts what is within the parens in the REASON field.

See screenshot for what my data looks like.

Hope this helps!!

[focosi]

I just tried this in a new field in a query:
InParens: Mid([REASON],InStr([REASON],"(")+1,(InStr([REASON],")")-1)-InStr([REASON],"("))

The field 'InParens' extracts what is within the parens in the REASON field.

See screenshot for what my data looks like.

Hope this helps!!

Thanks a ton Robeen. You helped me a lot. "Mid" and "InStr" functions were good hints. Actually my task is a little more complicated since :
1) I'm using query by form
2) my field contains multiple round parentheses.

Anyway :

1) I replaced "(" in your syntax with [Forms]![Form_name]![search_field_name" and the start_position in the Mid function is correctly found
2) in the number_of_characters part of the Mid function, I can make the same replacement for "(", but I still can't understand how to represent the next ")" in the field... (as you can see I have multiple pairs of parentheses in each field, and the number of characters between each pair of parentheses varies from 3 to 5)

Any hint to fix this last part ?
Thanks again

[June7]

Do you need to parse all the values from each pair of parens or only for B27?

[focosi]

Do you need to parse all the values from each pair of parens or only for B27?

Only for the parameter inputted in the query by form (B27 in my example). And each parameter occurs only once in the string.

[June7]

Try:

x = "A32(2000),B27(1000),C19(2000),DR20(1000)"
y = "C19"
Val(Mid(x,Instr(x,y)+Len(y)+1))

I use x and y for convenience. You will of course reference your field and form input.

[focosi]

Try:

x = "A32(2000),B27(1000),C19(2000),DR20(1000)"
y = "C19"
Val(Mid(x,Instr(x,y)+Len(y)+1))

I use x and y for convenience. You will of course reference your field and form input.

Wow, I was not aware of the "Val" function ! Works like a charm. Thanks a ton.