help needed with InStr() function

[mshmyob]

Here is a query that will extract your OD and ID out of your string.

Assuming you have a table (tblDimensions) with a field holding your string of dimensions and other text (DimDesc).

This query will extract all the OD and ID dimensions including the quote for inches and leave the ID blank if there is no ID.

[code=vb]
SELECT tblDimensions.D imDesc, Mid([DimDesc],1,InStr(1,[DimDesc],'"')) AS OD, IIf(InStr(1,[DimDesc],'x')<>0,Trim(( Mid([DimDesc],InStr(1,[DimDesc],'x')+1,InStr(1 ,[DimDesc],'"'))))) AS ID
FROM tblDimensions;
[/code]

cheers,

[kini113]

It didn't work can you look and see if I missed some thing
This is how the code came out with my the table name Bar Stock Inventory and column name DESCRIPTION

SELECT [Bar Stock Inventory].DESCRIPTION, 

Mid([Bar Stock Inventory].DESCRIPTION,1,
InStr(1,[Bar Stock Inventory].DESCRIPTION,"")) AS [OD SIZE],

IIf(InStr(1,[Bar Stock Inventory].DESCRIPTION,"x")<>0, 
Trim((Mid([Bar Stock Inventory].DESCRIPTION,
Instr(1,[Bar Stock Inventory].DESCRIPTION,"x")+1,
InStr(1,[Bar Stock Inventory].DESCRIPTION,""))))) AS [ID SIZE]

FROM [Bar Stock Inventory];

But this is what I got as the result:

DESCRIPTION            OD SIZE  ID SIZE
4.00" od"                  4
5.25" od x 2.500" id       5  
5.25" od x 2.500" id       5  
8.00" od x 5.00" id        8  
9.500" od x 7.00" id       9  
9.500" od x 7.00" id       9  
9.500" od x 7.00" id       9  
9.500" od x 7.00" id       9  
9.500" od x 7.00" id       9  
9.500" od x 7.00" id       9  
9.500" od x 7.00" id       9  
9.500" od x 7.00" id       9

[kini113]

The columns shifted to left when I posted

[mshmyob]

It didn't work can you look and see if I missed some thing
This is how the code came out with my the table name Bar Stock Inventory and column name DESCRIPTION

SELECT [Bar Stock Inventory].DESCRIPTION,

Mid([Bar Stock Inventory].DESCRIPTION,1,
InStr(1,[Bar Stock Inventory].DESCRIPTION,"" )) AS [OD SIZE],

IIf(InStr(1,[Bar Stock Inventory].DESCRIPTION,"x ")<>0,
Trim((Mid([Bar Stock Inventory].DESCRIPTION,
Instr(1,[Bar Stock Inventory].DESCRIPTION,"x ")+1,
InStr(1,[Bar Stock Inventory].DESCRIPTION,'" '))))) AS [ID SIZE]

FROM [Bar Stock Inventory];


But this is what I got as the result:

DESCRIPTION OD SIZE ID SIZE
4.00" od" 4
5.25" od x 2.500" id 5
5.25" od x 2.500" id 5
8.00" od x 5.00" id 8
9.500" od x 7.00" id 9
9.500" od x 7.00" id 9
9.500" od x 7.00" id 9
9.500" od x 7.00" id 9
9.500" od x 7.00" id 9
9.500" od x 7.00" id 9
9.500" od x 7.00" id 9
9.500" od x 7.00" id 9

Your quotes have to be '"' - ie: single quote then double quote then single quote.

[code=vb]
SELECT [Bar Stock Inventory].DESCRIPTION,

Mid([Bar Stock Inventory].DESCRIPTION,1,
InStr(1,[Bar Stock Inventory].DESCRIPTION,'" ')) AS [OD SIZE],

IIf(InStr(1,[Bar Stock Inventory].DESCRIPTION,"x ")<>0,
Trim((Mid([Bar Stock Inventory].DESCRIPTION,
Instr(1,[Bar Stock Inventory].DESCRIPTION,"x ")+1,
InStr(1,[Bar Stock Inventory].DESCRIPTION,'" '))))) AS [ID SIZE]

FROM [Bar Stock Inventory];

[/code]
cheers,

[kini113]

Thank you so much it's perfect

Cindy

[mshmyob]

Thank you so much it's perfect

Cindy

On behalf of Aj and Neo, you're welcome. Good luck.

cheers,

[NeoPa]

On behalf of Aj and Neo, you're welcome. Good luck.

cheers,

Thanks for picking up Msh. I was kept quite busy today.

[ajalwaysus]

Yes, thank you. I have been swamped at work.

-AJ