extract a string from a text field?

[Guest]

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))

 

[Guest]

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)

 

[Joseph Meehan]

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.

Can we assume you want to do this so you can properly normalize the
table? I sure looks you have have a design error right now.

 

[Guest]

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)

 

[Guest]

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)