Split a field into two using a query

[Guest]

We have a database which has equipment identities stored in a database column
named TAG the identity is made up of a series of letters followed by numbers
examples:

V1234 PC12345 PSV123

What we are looking to do is to use a query to SPLIT the field into two
separate fields TAG_FCTN and TAG_NO (already set up in the table) the letters
should go into the TAG_FCTN column and the number into the TAG_NO column.

I have used the split command in other cases but I am not sure how to find
the point between the letters and the numbers as there is no other delimiter
and it is not possible to use one with our clients numbering system.

The ID always follows the same format with the letters before the numbers
however the length of each can vary the letters are either 1,2 or 3 caracters
long and the numbers will be 3, 4 or 5 caracters long.

How do I find the change from numbers to letters and use that in a Split
command within a query (if that is the best way to do it, maybe it should be
VB, although I am not very proficient at VB)

Access version is 2003

Thanks in advance

Steve

 

[Guest]

Steve:

Create two functions in a standard module. In each walk through the TAG
value, extracting the non-numbers in one and the numbers in the other by
means of the IsNumeric function:

Public Function GetFCTN(strTag As String) As String

Dim strFCTN As String, strChr As String
Dim n As Integer

For n = 1 To Len(strTag)
strChr = Mid$(strTag, n, 1)
If Not IsNumeric(strChr) Then
strFCTN = strFCTN & strChr
End If
Next n

GetFCTN = Trim(strFCTN)

End Function

and:

Public Function GetNO(strTag As String) As String

Dim strNO As String, strChr As String
Dim n As Integer

For n = 1 To Len(strTag)
strChr = Mid$(strTag, n, 1)
If IsNumeric(strChr) Then
strNO = strNO & strChr
End If
Next n

GetNO = Trim(strNO)

End Function

You can then call them in a query:

SELECT GetFCTN(TAG) as TAG_FCTN, GetNO(TAG) as TAG_NO
FROM YourTable;

Ken Sheridan
Stafford, England

 

[Marshall Barton]

We have a database which has equipment identities stored in a database column
named TAG the identity is made up of a series of letters followed by numbers
examples:

V1234 PC12345 PSV123

What we are looking to do is to use a query to SPLIT the field into two
separate fields TAG_FCTN and TAG_NO (already set up in the table) the letters
should go into the TAG_FCTN column and the number into the TAG_NO column.

I have used the split command in other cases but I am not sure how to find
the point between the letters and the numbers as there is no other delimiter
and it is not possible to use one with our clients numbering system.

The ID always follows the same format with the letters before the numbers
however the length of each can vary the letters are either 1,2 or 3 caracters
long and the numbers will be 3, 4 or 5 caracters long.

How do I find the change from numbers to letters and use that in a Split
command within a query (if that is the best way to do it, maybe it should be
VB, although I am not very proficient at VB)

Access version is 2003

This is a little crude, but is probably easiest in a one
time operation.

In your Update query

SET
TAG_FCTN = Switch(Tag Like "[a-z][a-z][a-z]*",Left(Tag,3),
Tag Like "[a-z][a-z]*", Left(Tag,2),
Tag Like "[a-z]*", Left(Tag,1),
True,"Whoops"),
TAG_NO = Switch(Tag Like "[a-z][a-z][a-z]*", Mid(Tag,4),
Tag Like "[a-z][a-z]*", Mid(Tag,3),
Tag Like "[a-z]*", Mid(Tag,2),
True,Tag)

 

[Guest]

Thanks Ken

That did the trick, however I now have the data in a select query is it
possible to use the code in an update query (rather tha Select) so that I can
update the original table. I guess I could use a separate update query now
that I have the data in a select query but that seems like a long way round.

Thanks for the help

Steve
East Yorkshire

 

[Guest]

Steve:

You can call the functions in much the same way to update the values of the
other two columns:

UPDATE YourTable
SET TAG_FCTN = GetFCTN(TAG), TAG_NO = GetNO(TAG);

That's fine if you are going to delete the original TAG column from the
table definition, but if you are intending to keep the TAG column don't add
the other two columns; just use the functions to get the values whenever
required. You can call them in forms or reports just as you can in a query
If you have the original TAG column and the other two columns in a base table
that's redundancy which is *a bad thing* because it leaves the door open to
update anomalies.

Ken Sheridan
Stafford, England