 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / March 2007Extract numbers
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 > Hi all, > I have a query that contains a field(subject) that always has data [quoted text clipped - 7 lines] > > 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?  SignatureJoseph Meehan Dia 's Muire duit >Hi all, > I have a query that contains a field(subject) that always has data like [quoted text clipped - 5 lines] >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] 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 [quoted text clipped - 12 lines] > > John W. Vinson [MVP] >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] 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 [quoted text clipped - 6 lines] > > John W. Vinson [MVP] >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] 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. Signaturehth 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 :) [quoted text clipped - 9 lines] >> >> John W. Vinson [MVP] Hi "Al Campagna", I got an Error# =Mid([subject],InStr([subject],"2-")+2,(InStr([subject]," - N")-2)-InStr([subject],"2-")) > Pietro, > Try, [quoted text clipped - 14 lines] > >> > >> John W. Vinson [MVP] 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... Signaturehth 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# [quoted text clipped - 21 lines] >> >> >> >> John W. Vinson [MVP] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.