Problem with MID() function

[kini113]

I'm trying to split a text field ex: (10.00" od x 7.50" id) into 2 fields putting everything before the "x" into one column and everything after into another. I was able to achieve that using the Left() and Mid() functions. But I've run into a problem not all of the fields have an "x" some just have one dimension ex: (3.50" od) and the Mid() function returns that into the 2nd column, I need that 2nd column to be Null if there is no "x". This is what i have so far

ID SIZE: Mid([Bar Stock Inventory].[DESCRIPTION],IIf(InStr([Bar Stock Inventory].[DESCRIPTION],"x")>0,0,0)+1, +10)

I'm pretty sure I have the right formula but I don't know what to put instead of >0,0,0

HELP

[Delerna]

Check for the existence of x and if it exists then return the function result otherwise return null

PS. That dosn't look like TSQL syntax.

[kini113]

Sorry had it under the wrong topic heading it's not TSQL it's MS SQL

[ck9663]

Post what you have so far.

--- CK

[kini113]

For the OD size this is what i have and it's working great.

OD SIZE: Left([Purchase Info Bar Inventory]!DESCRIPTION,In Str([Purchase Info Bar Inventory]!DESCRIPTION,"o d")+1)

For the ID Size this is working somewhat it gives me the ID size when there is an ID size seperated by an "x" but if there is no ID size and no "x" it repeats the OD size instead of giving me a Null value ( this is my problem i need the Null value)

ID SIZE: Mid([Purchase Info Bar Inventory]!DESCRIPTION,In Str([Purchase Info Bar Inventory]!DESCRIPTION,"x ")+1,+10)

All of the this is in SQL format to be used in a query

I tried using both the Right() and Len() function but since my data is inconsistant I opted for the Mid() and "x" is unique to the field it is only used between the OD and ID size that is why I picked it for equation
This is a sample of the data which is imported from other softeware and there is no set format for entry into that program.

10.00" od x 7.50" id
10.00" od (solid bar of metal)
10.000" od x 7.500" id
10" od x 7.5" id
10.00" od x 7.50" id rough turned

Thanks for any help you can give

Cindy

[Delerna]

That looks like MS ACCESS syntax

in which case something like the following pseudo code
[code=sql]
[ID Size]:
iif(FormulaToCh eckIfXExistInDe scription
,FormulaToRetri eveTheIDSize
,"")
[/code]

I think your Instr formula will return 0 if x dosn't exist and its position within Description if it does.

Or is it -1 if it does not exist. I can't remember offhand.
Create a new field temporarily with the instr function alone and you will soon see what value to test for

[ck9663]

Use PATINDEX() to get the position of the "X", then substring up to that value-1 for the first field and get substring from that value+1 to the end of string to get the second.

Good luck!!

-- CK