Access Access IIF and Instr function

J

jwaccess

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!
 
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!
 
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
 
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.
 
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
 
Back
Top