By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,413 Members | 1,594 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,413 IT Pros & Developers. It's quick & easy.

Problem with MID() function

P: 27
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

Aug 18 '09 #1
Share this Question
Share on Google+
6 Replies

Expert 100+
P: 1,134
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.
Aug 18 '09 #2

P: 27
Sorry had it under the wrong topic heading it's not TSQL it's MS SQL
Aug 19 '09 #3

Expert 2.5K+
P: 2,878
Post what you have so far.

--- CK
Aug 20 '09 #4

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

OD SIZE: Left([Purchase Info Bar Inventory]!DESCRIPTION,InStr([Purchase Info Bar Inventory]!DESCRIPTION,"od")+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,InStr([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

Aug 20 '09 #5

Expert 100+
P: 1,134
That looks like MS ACCESS syntax

in which case something like the following pseudo code
Expand|Select|Wrap|Line Numbers
  1. [ID Size]:
  2. iif(FormulaToCheckIfXExistInDescription
  3. ,FormulaToRetrieveTheIDSize
  4. ,"")
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
Aug 20 '09 #6

Expert 2.5K+
P: 2,878
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
Aug 22 '09 #7

Post your reply

Sign in to post your reply or Sign up for a free account.