Extract numbers

[Guest]

Hi all,
I have a query that contains a field(subject) that always has data like
this:
SM# 2-1009579390 - New Industry report

I need to make anthother field in the query to extract only the numbers of
this field,knowing that the length of this number may differ from record to
record.
If it's possible,i need to extract only the numbers that come after 2-

Thanx

 

[Joseph Meehan]

Hi all,
I have a query that contains a field(subject) that always has data
like this:
SM# 2-1009579390 - New Industry report

I need to make anthother field in the query to extract only the
numbers of this field,knowing that the length of this number may
differ from record to record.
If it's possible,i need to extract only the numbers that come after 2-

Thanx

Are they always (100%) in the format XXXXXXNNNN...

This is they all will have exactly six characters (letters, numbers,
spaces or other characters) followed directly by the numbers you want and
nothing else?

 

[John W. Vinson]

Hi all,
I have a query that contains a field(subject) that always has data like
this:
SM# 2-1009579390 - New Industry report

I need to make anthother field in the query to extract only the numbers of
this field,knowing that the length of this number may differ from record to
record.
If it's possible,i need to extract only the numbers that come after 2-

?Mid([subject], InStr([subject], "2-") + 2, InStr(InStr([subject], "2-"),
[subject], " ")-6)

will do it if the part before the number is always 6 bytes long.

John W. Vinson [MVP]

 

[Guest]

Thank you for your answer..,
but actually the problem is that the part before the number is NOT always 6
bytes long,so can i do it or not ?

Hi all,
I have a query that contains a field(subject) that always has data like
this:
SM# 2-1009579390 - New Industry report

I need to make anthother field in the query to extract only the numbers of
this field,knowing that the length of this number may differ from record to
record.
If it's possible,i need to extract only the numbers that come after 2-

?Mid([subject], InStr([subject], "2-") + 2, InStr(InStr([subject], "2-"),
[subject], " ")-6)

will do it if the part before the number is always 6 bytes long.

John W. Vinson [MVP]

 

[John W. Vinson]

Thank you for your answer..,
but actually the problem is that the part before the number is NOT always 6
bytes long,so can i do it or not ?

You can use another InStr() to calculate the position - I'm too sleepy tonight
to figure it out, but if you type Ctrl-G to open the VBA editor, hit F1 to get
VBA help, and search for InStr you'll find detailed specs for how the function
works.

John W. Vinson [MVP]

 

[Guest]

I tried to do this,but i got lost,so please sleep well for now,i'm waiting
for your help when you wake up

 

[John W. Vinson]

I tried to do this,but i got lost,so please sleep well for now,i'm waiting
for your help when you wake up

sorry... not going to be able to get time for this anytime soon, I'm getting
ready for a 1200 mile drive and will be offline for a week.

I'd suggest you start a new thread with the expression so far.

John W. Vinson [MVP]

 

[Al Campagna]

Pietro,
Try,
=Mid([subject],InStr([subject],"2-")+2,(InStr([subject]," - N")-2)-InStr([subject],"2-"))
If this doesn't work, cut and paste your expression exactly when you reply.

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[Guest]

Hi "Al Campagna",
I got an Error#

=Mid([subject],InStr([subject],"2-")+2,(InStr([subject]," -
N")-2)-InStr([subject],"2-"))

Pietro,
Try,
=Mid([subject],InStr([subject],"2-")+2,(InStr([subject]," - N")-2)-InStr([subject],"2-"))
If this doesn't work, cut and paste your expression exactly when you reply.

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

I tried to do this,but i got lost,so please sleep well for now,i'm waiting
for your help when you wake up

 

[Al Campagna]

Pietro,
The calculation is correct, and tested. It will return the number portion... of "SM#
2-1009579390 - New Industry report"... no matter how many characters precede the "2-"

1. Use your own object names in the calculation. I used [Subject] as an example. Use
the name of your field that you are parsing in the calculation.
2. The name of the calculated field can not be the same as any element in the
calculation itself. If you named the calculated field [Subject], the that's why you're
getting an #Error. Call the Field something like "ParseSM", or "ExtractedSMNo", etc...

Other than that, there is something about your setup that you have not informed us
of...

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Hi "Al Campagna",
I got an Error#

=Mid([subject],InStr([subject],"2-")+2,(InStr([subject]," -
N")-2)-InStr([subject],"2-"))

Pietro,
Try,
=Mid([subject],InStr([subject],"2-")+2,(InStr([subject]," -
N")-2)-InStr([subject],"2-"))
If this doesn't work, cut and paste your expression exactly when you reply.

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

I tried to do this,but i got lost,so please sleep well for now,i'm waiting
for your help when you wake up

:

Thank you for your answer..,
but actually the problem is that the part before the number is NOT always 6
bytes long,so can i do it or not ?

You can use another InStr() to calculate the position - I'm too sleepy tonight
to figure it out, but if you type Ctrl-G to open the VBA editor, hit F1 to get
VBA help, and search for InStr you'll find detailed specs for how the function
works.

John W. Vinson [MVP]