473,324 Members | 2,254 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

help needed with InStr() function

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

Expand|Select|Wrap|Line Numbers
  1. ID SIZE: Mid([Bar Stock Inventory].[DESCRIPTION],IIf(InStr([Bar Stock Inventory].[DESCRIPTION],"x")>0,0,0)+1,+10)
  2.  
I'm pretty sure I have the right formula but I don't know what to put instead of >0,0,0

HELP
Aug 19 '09 #1
22 7705
ajalwaysus
266 Expert 100+
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.

Expand|Select|Wrap|Line Numbers
  1. Sub Test()
  2. Dim strFirst As String
  3. Dim strLast As String
  4. Dim strSample As String
  5.  
  6. strSample = "10.00' od x 7.50' id"
  7.  
  8. If InStr(1, strSample, "x") <> 0 Then
  9.     strFirst = Left(strSample, InStr(1, strSample, "x") - 1)
  10.     strLast = Right(strSample, Len(strSample) - InStr(1, strSample, "x"))
  11. End If
  12.  
  13. strFirst = Trim(strFirst)
  14. strLast = Trim(strLast)
  15.  
  16. End Sub
  17.  
-AJ
Aug 19 '09 #2
kini113
27
What would that be in simple expression form that I can put into my query?
Aug 19 '09 #3
ajalwaysus
266 Expert 100+
Sorry let me put this in the form of a field name:

Expand|Select|Wrap|Line Numbers
  1. 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])
  2.  
  3. 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)
  4.  
You may need to error check, but this is the gist of it.

-AJ
Aug 19 '09 #4
kini113
27
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)
Aug 19 '09 #5
kini113
27
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,InStr([Bar Stock Inventory].DESCRIPTION,"x")+1,+10)
Aug 19 '09 #6
ajalwaysus
266 Expert 100+
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
Aug 19 '09 #7
kini113
27
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.
Aug 19 '09 #8
NeoPa
32,556 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. [Bar Stock Inventory].[DESCRIPTION]  OD SIZE    ID SIZE
  2. 10.00" od x 7.50" id                 10.00"     7.50"
  3. 10.00" od x 7.50" id Extension       10.00"     7.50"
  4. 10.00" od                            10.00"     Null
  5. 10.00" od x 7.50" id                 10.00" od  7.50" id
  6. 10.00" od x 7.50" id Extension       10.00" od  7.50" id
  7. 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.
Aug 19 '09 #9
kini113
27
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
Aug 19 '09 #10
kini113
27
How can I get the [OD SIze] and [ID Size] from the description in lines 5 and 7?
Aug 19 '09 #11
NeoPa
32,556 Expert Mod 16PB
@kini113
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.
Aug 19 '09 #12
kini113
27
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
Aug 19 '09 #13
NeoPa
32,556 Expert Mod 16PB
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?
Aug 20 '09 #14
kini113
27
Yes I need SQL (within a query)
Thanks so much for your help
Aug 20 '09 #15
mshmyob
904 Expert 512MB
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT tblDimensions.DimDesc, 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
  2. FROM tblDimensions;
  3.  
cheers,
Aug 20 '09 #16
kini113
27
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
Expand|Select|Wrap|Line Numbers
  1. SELECT [Bar Stock Inventory].DESCRIPTION, 
  2.  
  3. Mid([Bar Stock Inventory].DESCRIPTION,1,
  4. InStr(1,[Bar Stock Inventory].DESCRIPTION,"")) AS [OD SIZE],
  5.  
  6. IIf(InStr(1,[Bar Stock Inventory].DESCRIPTION,"x")<>0, 
  7. Trim((Mid([Bar Stock Inventory].DESCRIPTION,
  8. Instr(1,[Bar Stock Inventory].DESCRIPTION,"x")+1,
  9. InStr(1,[Bar Stock Inventory].DESCRIPTION,""))))) AS [ID SIZE]
  10.  
  11. FROM [Bar Stock Inventory];
But this is what I got as the result:
Expand|Select|Wrap|Line Numbers
  1. DESCRIPTION            OD SIZE  ID SIZE
  2. 4.00" od"                  4
  3. 5.25" od x 2.500" id       5  
  4. 5.25" od x 2.500" id       5  
  5. 8.00" od x 5.00" id        8  
  6. 9.500" od x 7.00" id       9  
  7. 9.500" od x 7.00" id       9  
  8. 9.500" od x 7.00" id       9  
  9. 9.500" od x 7.00" id       9  
  10. 9.500" od x 7.00" id       9  
  11. 9.500" od x 7.00" id       9  
  12. 9.500" od x 7.00" id       9  
  13. 9.500" od x 7.00" id       9
Aug 20 '09 #17
kini113
27
The columns shifted to left when I posted
Aug 20 '09 #18
mshmyob
904 Expert 512MB
@kini113

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

Expand|Select|Wrap|Line Numbers
  1. SELECT [Bar Stock Inventory].DESCRIPTION, 
  2.  
  3. Mid([Bar Stock Inventory].DESCRIPTION,1,
  4. InStr(1,[Bar Stock Inventory].DESCRIPTION,'"')) AS [OD SIZE],
  5.  
  6. IIf(InStr(1,[Bar Stock Inventory].DESCRIPTION,"x")<>0, 
  7. Trim((Mid([Bar Stock Inventory].DESCRIPTION,
  8. Instr(1,[Bar Stock Inventory].DESCRIPTION,"x")+1,
  9. InStr(1,[Bar Stock Inventory].DESCRIPTION,'"'))))) AS [ID SIZE]
  10.  
  11. FROM [Bar Stock Inventory];
  12.  
  13.  
cheers,
Aug 20 '09 #19
kini113
27
Thank you so much it's perfect

Cindy
Aug 20 '09 #20
mshmyob
904 Expert 512MB
@kini113
On behalf of Aj and Neo, you're welcome. Good luck.

cheers,
Aug 20 '09 #21
NeoPa
32,556 Expert Mod 16PB
@mshmyob
Thanks for picking up Msh. I was kept quite busy today.
Aug 20 '09 #22
ajalwaysus
266 Expert 100+
Yes, thank you. I have been swamped at work.

-AJ
Aug 20 '09 #23

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

Similar topics

2
by: Chris Michael | last post by:
Hello everybody, Newbie here. I've been working on this for the last two days and I can't figure out where this problem is. I think it's something so obvious, but I can't see it! OK, firstly...
4
by: Steve | last post by:
Hi, I have a table which contains records of user access and searches made within an application, this is a sample of the data: response for 101 results from database in 28906 ms I only...
4
by: Mark | last post by:
the Following bit of code doesn't work. It seems to respond to the second, starting with 'add iif statement for Good Practice', but not to the first, starting 'add iif statement for archived' ...
2
by: Johnny Gardner | last post by:
I'm hoping this is simple: field1 | field2 abc-123 def-456 abc-123 I need to update field2 to everything that comes after the dash in field 1. So final result would be
5
by: Tim::.. | last post by:
Hi can someone please tell me how I change this directory service query so that it searches through each record in the active directory and returns all the accounts! At the moment I can only get...
2
by: Tony Ciconte | last post by:
Does anyone know of or have any VBA code or similar logic that can help distinguish similar first/last name combinations? For example, we would like to prompt the user of a possible match when any...
6
by: JonathanOrlev | last post by:
Hello everyone, I have a newbe question: In Access (2003) VBA, what is the difference between a Module and a Class Module in the VBA development environment? If I remember correctly, new...
3
by: frankleggett | last post by:
Hi I am a bit confused why this if then statement doesn't work. If InStr(Result(12), "CF") Or InStr(Result(12), "cf") And Result(2) <> "1st=" Then GoTo WinnerCF If InStr(Result(12), "CF") Or...
4
by: thesinnerishere | last post by:
i have created a program(link-generator) which is static in nature(it works correctly) meaning that this program must find the symbol { } first for it to operate properly.how can i make it dynamic so...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.