Query Question For Parsing A Character String Separated By "-"

[rustydud]

Hello all!
I'm a newbie to this thread, I am learning a bit about Access 2010 and have a riddle for you.
I have a column named INTOOLS_TAG with the following character strings (note: there are more than 1000 entries in this column, and below is a strong representation of the kinds of character strings the column):
6411-ISV-5901-1
6411-PI--5615-1
6411-PE-5006-1A
6411-SG--5006-1
6411-TI-5008-11
6411-LE-6006-1B

I need to extract the following strings from each of these entries in INTOOLS_TAG and place them in column with title "APPLICABLE SYSTEM":
INTOOLS_TAG APPLICABLE SYSTEM
6411-ISV-5901-1 --> 5901-1
6411-PI--5615-1 --> 5615-1
6411-PE-5006-1A --> 5006-1A
6411-SG--5006-1 --> 5006-1
6411-TI-5008-11 --> 5008-11
6411-LE-6006-1B --> 6006-1B

In my "Design View" for this query, I need to place the proper code into the "Field" entry in order to generate column "APPLICABLE SYSTEM".


Can you help me with this?
Thanks in advance!

[orange]

I would recommend you read this article on relational design principles. You will see the importance of atomic data.
http://forums.aspfree.com/attachment...2&d=1201055452

[June7]

I agree with orange's suggestion, store data properly and this would not be an issue. It is easier to concatenate than break apart. Fixing the data would be fairly easy if the structure of value is always the same. However, the inconsistency of the second part (ISV, PI-, TI) complicates as well as case that the part you want begins with different numbers. I suppose a custom function in a general module could handle the inconsistencies, something like.

Function GetPart(strS As String) As String
Dim aryS As Variant
aryS = Split(Replace(strS,"--","-"),"-")
GetPart = aryS(2) & "-" & aryS(3)
End Function

Now call the function from query:

Part: GetPart([INTOOLS_TAG])

[rustydud]

Hello
Thanks for the comments!
I will try to explain my next question as best as I can.
I am entering all data using the "Design View" of the Query. I am using Access 2010. Just as an example, in one of the columns I have in the "Field" row the following: NumberOfCharINTOOLS: Len([INTOOLS_TAG]).

How can I enter that custom function as suggested by June7 into my query using the "Design View" of the Query?

I agree with orange's suggestion, store data properly and this would not be an issue. It is easier to concatenate than break apart. Fixing the data would be fairly easy if the structure of value is always the same. However, the inconsistency of the second part (ISV, PI-, TI) complicates as well as case that the part you want begins with different numbers. I suppose a custom function in a general module could handle the inconsistencies, something like.

Function GetPart(strS As String) As String
Dim aryS As Variant
aryS = Split(Replace(strS,"--","-"),"-")
GetPart = aryS(2) & "-" & aryS(3)
End Function

Now call the function from query:

Part: GetPart([INTOOLS_TAG])

[June7]

You are not entering data in query design view, you are building a query to manipulate data.

The function goes in a VBA module. Call the function from the query. Calling the function is same as creating a field in query with an expression. The function call is the expression.

[rustydud]

You are not entering data in query design view, you are building a query to manipulate data.

The function goes in a VBA module. Call the function from the query. Calling the function is same as creating a field in query with an expression. The function call is the expression.

You guys are awesome. I think I am getting somewhere.
I entered the function in the VBA module. I called the function from the query
"Part: GetPart([INTOOLS_TAG])"
as you instructed.

When I go in the "Datasheet View" of the query, the column "Part" shows EXACTLY the information I need. This is good!

But here's my issue. I go back in "Design View" for my query. For the Field "Part: GetPart([INTOOLS_TAG])", I change the table to "INSTRUMENTS" which is where I want the information for column "Part" to show up. I try to "save" and "close" and get the following error:

"Extra ) in query expression 'INSTRUMENTS.[GetPart([INTOOLS_TAG])]'. "

I do not see the extra ")" and I've tried to delete the existing ")". I can't resolve this issue. What can I do to try and resolve this?

[June7]

That will not save data to table. Must use an UPDATE query to commit the values into table.