help needed with InStr() 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

[ajalwaysus]

Try something like this instead, this way you assign each side to a value if an X exists, or else you just take the whole value.

Sub Test()
Dim strFirst As String
Dim strLast As String
Dim strSample As String

strSample = "10.00' od x 7.50' id"

If InStr(1, strSample, "x") <> 0 Then
    strFirst = Left(strSample, InStr(1, strSample, "x") - 1)
    strLast = Right(strSample, Len(strSample) - InStr(1, strSample, "x"))
End If

strFirst = Trim(strFirst)
strLast = Trim(strLast)

End Sub

-AJ

[kini113]

What would that be in simple expression form that I can put into my query?

[ajalwaysus]

Sorry let me put this in the form of a field name:

OD SIZE: IIF(InStr(1,[Bar Stock Inventory].[DESCRIPTION], 0) <> 0, Left([Bar Stock Inventory].[DESCRIPTION], InStr(1, [Bar Stock Inventory].[DESCRIPTION], "x") - 1), [Bar Stock Inventory].[DESCRIPTION])

ID SIZE: IIF(InStr(1,[Bar Stock Inventory].[DESCRIPTION], 0) <> 0, Right([Bar Stock Inventory].[DESCRIPTION], Len([Bar Stock Inventory].[DESCRIPTION]) - InStr(1, [Bar Stock Inventory].[DESCRIPTION], "x")), null)

You may need to error check, but this is the gist of it.

-AJ

[kini113]

It did pull some Null values but some where correct and others weren't and I can't figure out why. I didn't use the Len() function before since i'm trying to get rid of extra text i don't want at the end ex: (12.50" od x 7.00" id rough turned capable of 38-42)

[kini113]

If I run the IIF() first and have it look for "x" as a true false statement which comparison operater would i use since "= or <>" are the only ones i've seen used before
ID SIZE: IIF([Bar Stock Inventory]![DESCRIPTION] ?? ,Mid([Bar Stock Inventory].DESCRIPTION,In Str([Bar Stock Inventory].DESCRIPTION,"x ")+1,+10)

[ajalwaysus]

I think you may be setting yourself up for issues down the road, if you are storing more text then just the (12.50" od x 7.00") then what is to say that an "X" won't be inputted before or after the text you wish to evaluate?

i.e.
(12.50" od x 7.00" experience)

-AJ

[kini113]

As part the of the creation of this database our purchaser has been given the format for all new entries(12.50" od x 7.50" id) and to put all other text in the notes field in the software that feeds this database. I also checked all the previous data for extra x's and didn't find any. So that shouldn't be a problem.

[NeoPa]

I'm afraid this question is quite unclear. What you're actually after is not expressed too well.

Would the following express your requirement :
You want the expression of inches (including the text "od" or maybe not) in one result field.
If, after this in the field ([Bar Stock Inventory].[DESCRIPTION]), there is an "x" (or maybe " x ") then you want the second result field to contain the following expression of inches (including or not the text "id").
If there is no "x" after the first expression then the second result expression should be left empty (Null).

Some examples :

[Bar Stock Inventory].[DESCRIPTION]  OD SIZE    ID SIZE
10.00" od x 7.50" id                 10.00"     7.50"
10.00" od x 7.50" id Extension       10.00"     7.50"
10.00" od                            10.00"     Null
10.00" od x 7.50" id                 10.00" od  7.50" id
10.00" od x 7.50" id Extension       10.00" od  7.50" id
10.00" od                            10.00" od  Null

Could you indicate which of these examples best express what it is you require from the expressions you need to create for the two result fields.

[kini113]

Lines 5 and 7 are examples of what kind of data i have but I also have a description that is (10.00" od x 7.50 id rough turned 38-43rc with certs). I don't have an "x" anywhere other then between the 2 sizes. So all the line above would be correct

[kini113]

How can I get the [OD SIze] and [ID Size] from the description in lines 5 and 7?

[NeoPa]

Lines 5 and 7 are examples of what kind of data i have but I also have a description that is (10.00" od x 7.50 id rough turned 38-43rc with certs). I don't have an "x" anywhere other then between the 2 sizes. So all the line above would be correct

I'm not sure your answer makes understanding this any easier.

Why is there no (") after 7.50 in your example?
How can lines 5 & 7 be remotely similar to your data if, as you say, they are followed by any further text (more description as in your example)?

Let's be very clear about this :
Before we can consider what code will work for this we must have a very accurate and precise understanding of what data may appear. Not just what is usual, but anything that might appear. We are, after all, talking of code. It has no inbuilt intelligence. It relies entirely on being designed exactly to match whatever can be thrown at it. It can be made to be very flexible, but not to be intelligently adaptive.

[kini113]

The description is for a bar of metal with an outer dimension (od) and an inner dimemsion (id). But if the bar is solid there is no id. So from that data which varies in format since it is imported from other software with no set format. I need two columns one for the od size and the other for id size and if there is no id then the field needs to be Null. If there is an "x" in the field it seperates an od and id. If there is no "x" then there is no id. Here are some examples of my data. I hope this is more clear then before.

10.00" od x 7.50" id
10.00" od
10.000" od x 7.500" id
10.00" od x 7.50" id rough turned

For my purpose it doesn't matter if I get 10.00, 10.00", or 10.00" od as long as when there is no id i get a null value in the ID size column. Any of those values would allow me to do further analysis

Thanks
Cindy

[NeoPa]

That's Crystal Cindy :)

It's a bit late here now so I can't do anything with this yet, but I can look again tomorrow with a clear understanding of what's required now.

Just to confirm, the code you require is SQL (within a query) rather than VBA yes?

[kini113]

Yes I need SQL (within a query)
Thanks so much for your help