extract a string from a text field?

[SusanJane sjl]

I have a text field that looks like:

45 lb premium mulch (345600)

and using an Access query I need to extract the number within the
parentheses. Isn't there a function I can use to do this? the length of the
text field is variable, of course, but the number I'm trying to extract is
also a variable length.

 

[Dirk Goldgar]

I have a text field that looks like:

45 lb premium mulch (345600)

and using an Access query I need to extract the number within the
parentheses. Isn't there a function I can use to do this? the
length of the text field is variable, of course, but the number I'm
trying to extract is also a variable length.

How reliable is the field format? If the number will always be there,
and will always be enclosed in parentheses, and will always be the first
item enclosed in parentheses, then you can use an expression like this:

Val(Mid([YourField], InStr([YourField], "(") + 1))

 

[SusanJane sjl]

Dirk,

That is PERFECT!!! Just what I needed. Thanks for you quick response.

I LOVE THE INTERNET!
--

I have a text field that looks like:

45 lb premium mulch (345600)

and using an Access query I need to extract the number within the
parentheses. Isn't there a function I can use to do this? the
length of the text field is variable, of course, but the number I'm
trying to extract is also a variable length.

How reliable is the field format? If the number will always be there,
and will always be enclosed in parentheses, and will always be the first
item enclosed in parentheses, then you can use an expression like this:

Val(Mid([YourField], InStr([YourField], "(") + 1))

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Tabatha]

Dirk,

I have a similar question, however, my data after my "-" sometimes starts
with a number & sometimes a letter. I am able to return the numbers, using
the code provided, however not the letters?

Any suggestions?

Thanks,
Tabatha

I have a text field that looks like:

45 lb premium mulch (345600)

and using an Access query I need to extract the number within the
parentheses. Isn't there a function I can use to do this? the
length of the text field is variable, of course, but the number I'm
trying to extract is also a variable length.

How reliable is the field format? If the number will always be there,
and will always be enclosed in parentheses, and will always be the first
item enclosed in parentheses, then you can use an expression like this:

Val(Mid([YourField], InStr([YourField], "(") + 1))

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Tabatha]

I figured it out on my own, I removed the Val from the beginning of the
formula.

Thanks anyway

Dirk,

I have a similar question, however, my data after my "-" sometimes starts
with a number & sometimes a letter. I am able to return the numbers, using
the code provided, however not the letters?

Any suggestions?

Thanks,
Tabatha

I have a text field that looks like:

45 lb premium mulch (345600)

and using an Access query I need to extract the number within the
parentheses. Isn't there a function I can use to do this? the
length of the text field is variable, of course, but the number I'm
trying to extract is also a variable length.

How reliable is the field format? If the number will always be there,
and will always be enclosed in parentheses, and will always be the first
item enclosed in parentheses, then you can use an expression like this:

Val(Mid([YourField], InStr([YourField], "(") + 1))

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)