 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / November 2005extract partial information
I have an Itemfield in my Itemtable, what I would like to do is extract some information from that Itemfield and put in a different table as follows itemfield example = DOC51110 , DFM51111, DOC5113, FSP51110, FSS51111 etc I would like to extract the first 3 left characters and put that information in tblItemDesc.LogCode I would like to extrat the 4 right characters and put that information in the tblItemDesc.ProductCode Log Cod Product Code DOC 1110 DFM 1111 DOC 5113 FSP 1110 FSS 1111 Use the Left() and Right() functions. Left ("DOC51110",3) --> "DOC" Right("DOC51110",4) --> "1110" You could use these in a query too. (example) Select Left(ItemField,3) as [Log Cod], Right(ItemField,4) as [Product Code] From Table1 HTH Rob Mastrostefano  SignatureFMS Professional Solutions Group http://www.fmsinc.com/consulting Software Tools for .NET, SQL Server, Visual Basic & Access http://www.fmsinc.com FMS Advanced Systems Group http://www.fmsasg.com/ >I have an Itemfield in my Itemtable, what I would like to do is extract >some [quoted text clipped - 14 lines] > FSP 1110 > FSS 1111 Thanks, but how am I going to put that informaion into a different table? and what if for example I have the first 3 as DOC123 how will this be taken care of? > Use the Left() and Right() functions. > [quoted text clipped - 30 lines] > > FSP 1110 > > FSS 1111 Well, I am assuming you have a table that has the same field names/sizes so you can create a query. Insert Into Table2 (ItemField) Select Distinct Left(ItemField,3) as [Log Cod], Right(ItemField,4) as [Product Code] From Table1 This will take the data from Table1 and put it into Table2 I believe the "Distinct" keyword in the query will help elimate the duplicates, which is what I believe the other part of your question was about. Give that a try and let me know how it works out for you. Rob Mastrostefano SignatureFMS Professional Solutions Group http://www.fmsinc.com/consulting Software Tools for .NET, SQL Server, Visual Basic & Access http://www.fmsinc.com FMS Advanced Systems Group http://www.fmsasg.com/ > Thanks, but how am I going to put that informaion into a different table? > and [quoted text clipped - 39 lines] >> > FSP 1110 >> > FSS 1111 Instead of the right(FieldName,3) I would use Mid(FieldName,4) that way it will take how many digits there are after the three text chr. SignatureI hope that helped Good Luck > Thanks, but how am I going to put that informaion into a different table? and > what if for example I have the first 3 as DOC123 how will this be taken care [quoted text clipped - 34 lines] > > > FSP 1110 > > > FSS 1111 Ignore my post for now You asked what will happen if you have DOC123, what do you want to happen with this? DOC and 123 or DOC and 23 ignore the 1 like you ignore the 5 in your example > Instead of the right(FieldName,3) I would use Mid(FieldName,4) that way it > will take how many digits there are after the three text chr. [quoted text clipped - 37 lines] > > > > FSP 1110 > > > > FSS 1111 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.