Access IIF and Instr function


Joined
Dec 26, 2012
Messages
3
Reaction score
0
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!
 
Ad

Advertisements

fx3

Joined
Feb 17, 2013
Messages
13
Reaction score
0
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))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access Access IIF and Instr function 6
InStr function 8
Using a combination of IIF, INSTR and LEFT 2
InStr Function 3
Help with InStr function 3
Memo Field Truncation 1
IIF function, multiple IIF 0
Update / Select query 6

Top