Extracting a number from record for a new field

tcarter963

New Member
Joined
Aug 3, 2006
Messages
38
I have a field called SampleName for which most of the records look something like this 00468-003-03 2 Days

I would like the extract the 2 from this field and put it in a new field called Days.

Using Access 2003 how would I go about doing something like that, or is it possible?

Thanks
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,092
Office Version
365
Platform
Windows
Assuming that it always follow the same format, like this:
text space number space text
to extract that number, use this code (where MyField is the name of your field):
Code:
NumDays: Mid[MyField],InStr([MyField]," ")+1,InStrRev([MyField]," ")-InStr([MyField]," ")-1)+0
 

tcarter963

New Member
Joined
Aug 3, 2006
Messages
38
Thanks a ton Joe.

I really hate to ask such a basic question, but I'm not as familiar with access as I am excel. Does this code go in Modules?

I'm mainly using this one table to pass data to excel, the trouble is getting in the right order. Using the day value in the sample name is much better than going off when the data was dumped into the database.

Yes, the format should always be text space number space text.

I created a field named NumDays and the field SampleName contains the records so the code should be as follows

NumDays: Mid[SampleName],InStr([SampleName]," ")+1,InStrRev([SampleName]," ")-InStr([SampleName]," ")-1)+0


Again, Thank you.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,092
Office Version
365
Platform
Windows
I really hate to ask such a basic question, but I'm not as familiar with access as I am excel. Does this code go in Modules?
No, what I gave you is just a calculated field that you would put in a query (using your table as its source).
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,948
Messages
5,508,316
Members
408,678
Latest member
ripperbolt

This Week's Hot Topics

Top