Determination of numerical value inside brackets

  • Thread starter Thread starter Gareth Jones
  • Start date Start date
G

Gareth Jones

I need a function to allow me to determine the numerical
value contained inside brackets in a text field.

E.g. the field may contain text like:

xyz54 (123)

I need the function to return the value of 123. There is
no set number of characters before the open or close of
the brackets.

Any help would be of great assistance, many thanks

Gareth
 
You could do this without writing a custom function by
combining/nesting the Instr() and Mid() functions.

Instr(1,[YourField],")",1) will give you the position of
the first ) - 7 in your example.

By nesting this to set the start position to one after
the first ), you can find the second occurence - 11 in
your example.

Instr(Instr(1,[YourField],")",1) + 1,[YourField],")",1)

Then put use these in the Mid function such as:

Mid([YourField],Instr(1,[YourField],")",1)+1,Instr(Instr
(1,[YourField],")",1) + 1,[YourField],")",1)-Instr(1,
[YourField],")",1)-1)

This is all air code, and I am rushing to make a 1:30
meeting, so there may be typos. Also, you will likely
encounter errors if some fields don't have the two
parenthesis. In that case you would likely have to throw
in the use of Nz() or iif() functions.

Of course you could also write a custom function to
basically do the same thing (it would be cleaner to
call). Post back if you would like help on doing that.

HTH, Ted Allen
 
IF you just want the value then you can use
Val(Mid(YourField,Instr(1,YourField,"(",1)+1)

That will return 1 for 001. In other words, leading zeroes will be dropped. I
might make this a bit more complex by testing for the presence of the "(" first

IIF(FieldName Like "*(#*",Val(Mid(YourField,Instr(1,YourField,"(",1)+1),YourField)

That will return YourField if no "(" is available in the string.

Ted said:
You could do this without writing a custom function by
combining/nesting the Instr() and Mid() functions.

Instr(1,[YourField],")",1) will give you the position of
the first ) - 7 in your example.

By nesting this to set the start position to one after
the first ), you can find the second occurence - 11 in
your example.

Instr(Instr(1,[YourField],")",1) + 1,[YourField],")",1)

Then put use these in the Mid function such as:

Mid([YourField],Instr(1,[YourField],")",1)+1,Instr(Instr
(1,[YourField],")",1) + 1,[YourField],")",1)-Instr(1,
[YourField],")",1)-1)

This is all air code, and I am rushing to make a 1:30
meeting, so there may be typos. Also, you will likely
encounter errors if some fields don't have the two
parenthesis. In that case you would likely have to throw
in the use of Nz() or iif() functions.

Of course you could also write a custom function to
basically do the same thing (it would be cleaner to
call). Post back if you would like help on doing that.

HTH, Ted Allen
-----Original Message-----
I need a function to allow me to determine the numerical
value contained inside brackets in a text field.

E.g. the field may contain text like:

xyz54 (123)

I need the function to return the value of 123. There is
no set number of characters before the open or close of
the brackets.

Any help would be of great assistance, many thanks

Gareth

.
Click to expand...
Click to expand...
 
Ted,

Have now got query to work due to your help with the functions below, many
thanks

Gareth

Ted Allen said:
You could do this without writing a custom function by
combining/nesting the Instr() and Mid() functions.

Instr(1,[YourField],")",1) will give you the position of
the first ) - 7 in your example.

By nesting this to set the start position to one after
the first ), you can find the second occurence - 11 in
your example.

Instr(Instr(1,[YourField],")",1) + 1,[YourField],")",1)

Then put use these in the Mid function such as:

Mid([YourField],Instr(1,[YourField],")",1)+1,Instr(Instr
(1,[YourField],")",1) + 1,[YourField],")",1)-Instr(1,
[YourField],")",1)-1)

This is all air code, and I am rushing to make a 1:30
meeting, so there may be typos. Also, you will likely
encounter errors if some fields don't have the two
parenthesis. In that case you would likely have to throw
in the use of Nz() or iif() functions.

Of course you could also write a custom function to
basically do the same thing (it would be cleaner to
call). Post back if you would like help on doing that.

HTH, Ted Allen
-----Original Message-----
I need a function to allow me to determine the numerical
value contained inside brackets in a text field.

E.g. the field may contain text like:

xyz54 (123)

I need the function to return the value of 123. There is
no set number of characters before the open or close of
the brackets.

Any help would be of great assistance, many thanks

Gareth

.
Click to expand...
Click to expand...
 
Back
Top