extract partial information

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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?

RobFMS said:
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

--
FMS 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/


JOM said:
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
Click to expand...
Click to expand...
 
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

--
FMS 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/


JOM said:
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?

RobFMS said:
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

--
FMS 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/


JOM said:
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
Click to expand...
Click to expand...
Click to expand...
 
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.


--
I hope that helped
Good Luck


JOM said:
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?

RobFMS said:
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

--
FMS 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/


JOM said:
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
Click to expand...
Click to expand...
Click to expand...
 
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



Ofer said:
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.


--
I hope that helped
Good Luck


JOM said:
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?

RobFMS said:
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

--
FMS 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
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top