Access IIF and Instr function

[jwaccess]

Hello Access 2007 Experts! I have one for you. I have a field [ITEM_INFO] where I need to pull out a set of numbers from a string and return the results. However, there are variations of the data. Here is a sample of the different types of data contained in the ITEM_INFO field:

Type 1: 03' TODDLER FOOD
Type 2: 10-0012-12ft-BABY FOOD

My goal is to return the 03 for Type 1 and 12 in Type 2 and give it a new field name called "SET_SIZE". I am thinking I will need to use a combination of IIF and Instr.

Would anyone be able to help in writing this expression in the query line?

Thank you so much!

 

[fx3]

mmm... I'd personally write a VBA script to do this using regular expressions, and make it a simple function call, but if you must, here is the IIF version of it, if your field is f.... pretty ugly but works, given you have consistent data

=IIF(ISERR(FIND("'",f)),MID(f,FIND("-",f,FIND("-",f)+1)+1,FIND("ft",f)-FIND("-",f,FIND("-",f)+1)-1),LEFT(f,FIND("'",f)-1))