Extract numbers

P

Pietro

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
 
J

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-
Click to expand...

?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]
 
P

Pietro

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 ?

John W. Vinson said:
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-
Click to expand...

?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]
Click to expand...
 
J

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 ?
Click to expand...

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]
 
P

Pietro

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

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 :)
Click to expand...

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]
 
A

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."
 
P

Pietro

Hi "Al Campagna",
I got an Error#

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



Al Campagna said:
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."


Pietro said:
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 :)
Click to expand...
Click to expand...
 
A

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."


Pietro said:
Hi "Al Campagna",
I got an Error#

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



Al Campagna said:
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."


Pietro said:
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]
Click to expand...
Click to expand...
Click to expand...
 
Top