Extracting a number from record for a new field

[tcarter963]

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

 

[Joe4]

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

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

 

[tcarter963]

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]

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

 

[tcarter963]

Thanks Joe, works great. I appreciate the help.