Access 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!

 

[Flona2012]

from where did u get 03 and 12, i didn't understand???
How did u determine these two numbers???

 

[jwaccess]

Fiona,

Thanks for attempting to respond. In reading my post, I can see it would be confusing. My goal is to return the set size which is expressed in "feet". However, the first example uses the character ' for "feet" and I want "03" in front of it (which is 3 feet). In the second example, I want "12" which is the number before the character "ft". We get this data from a customer and it is not very clean or consistent. Any help you can give would be greatly appreciated. Thanks so much!

 

[avanch]

Did you a reply for this question as I have the same kind of problem

 

[Flona2012]

actually am sorry but no solution crossed my mind, because i don't know if always as second example 12ft will have "-", i couldn't find key words to the thing but if you open new thread and explain your case i might be able to help you

 

[avanch]

I have several telephone numbers that come to me in different formats. Examples are:
(714) 562-3333
714--562-3333
(714) 562--3333
714-562-3333
714 562-3333
just a few.
I have to write a routine to go through this field character by character and extract only the number into another field. Now I am using instr, len(field) and mid functions and write several statements to do this. The problem being I have to run it one statement at a time. I was wondering if there is a way to write the loop statement(I am sure there is).
Thank you.

 

[Flona2012]

if the only characters used are ( ) - and space then i guess this sentence is much better than len and instr

Replace(
Replace(
Replace(
Replace(
Sheets("SheetName").Cells(RowNumber, ColumnNumber), "-", ""),
"(", "")
, ")", ""),
" ", "")

* The first replace is for "-" to replace with "" blank not space
* The first replace is for "(" to replace with "" blank not space
* The first replace is for ")" to replace with "" blank not space
* The first replace is for " " Space to replace with "" blank not space

Replace Structure: replace (TheString, The Character You want to be replaced, The new Character)

if only you'll have these 4 types of characters - ( ) space