get part of the name after 7th dot and isert into new column

[vojinb]

Hi,
I have a column (char type) consisting data like:
PL.EUR.51001.2003.RS.21059.RS.1605.T.1500

What I want is to based on value above to make insert into new column, but only data starting from 7th dot .
I have tried with mid function but the problem is that length of characters in column is not always same so I have data like:
PL.EUR.51001.2003.RS.21059.RS.1605.T.1500
PL.EUR.51001.2002.YU.6005.YU.1605..2100
where part of data "1605" starts on different char position, but what is for sure is that it always starts from 7th dot.
So how can I for example say :
Insert into my table(value)
select (data starting from 7th dot with length 4)
from my table 2

 

[Guus2005]

Using the function i posted some time ago named GetPart you can get the n'th part of a string given a certain separator.

You can modify it to your liking.

Search for GetPart on this forum.

 

[Guus2005]

if the number of dots are always the same you can simply use this query:

Insert into my table(value)
select Split([SomeField], ".")(7) & "." & Split([SomeField], ".")(8) & "." & Split([SomeField], ".")(9)
from my table 2

Basenumber of an array is default 0 so you might want to change the numbers in the above query.

HTH

 

[JANR]

Perhaps you could split the string into an array and extract the 8th element.

ex:

Function MySplitt()
Dim x As String
Dim var As Variant
x = "PL.EUR.51001.2003.RS.21059.RS.1605.T.500"
var = Split(x, ".")
Debug.Print var(7)
End Function

JR

 

[vojinb]

Hi ,
thanks for support. I've tried with query and result was
"Invalid use of '.' '!',or ()...etc"

I have found your function "getpart "but not sure how to use it ?

 

[Guus2005]

can you show the query or the code you use which generates the error?

 

[vojinb]

Hi,
here is query which I run from VBA:
INSERT INTO kategorija ( idbu, konto, naziv, katBilansaUspjeha, Industrycode, CA_Referent, katBilansaStanja, sektor )
SELECT id, konto, nazivpuni, mid(nazivpuni,8,5), mid(nazivpuni,37,4), mid(nazivpuni,14,4), mid(nazivpuni,22,5),(Split([nazivpuni], ".")(7) & "." & Split([nazivpuni], ".")(8) & "." & Split([nazivpuni], ".")(9))
FROM bilansuspjeha
WHERE nazivpuni like "PL.*";

With red I've marked column for inserting, and part of your query modified for my table

 

[Guus2005]

1. Remove the parenthesis around the split commands or
2. It is possible that you have to replace the double quotes with single quotes.

1:

INSERT INTO kategorija ( idbu, konto, naziv, katBilansaUspjeha, Industrycode, CA_Referent, katBilansaStanja, sektor )
SELECT id, konto, nazivpuni, mid(nazivpuni,8,5), mid(nazivpuni,37,4), mid(nazivpuni,14,4), mid(nazivpuni,22,5),Split([nazivpuni], ".")(7) & "." & Split([nazivpuni], ".")(8) & "." & Split([nazivpuni], ".")(9)
FROM bilansuspjeha
WHERE nazivpuni like "PL.*";

or 2:

INSERT INTO kategorija ( idbu, konto, naziv, katBilansaUspjeha, Industrycode, CA_Referent, katBilansaStanja, sektor )
SELECT id, konto, nazivpuni, mid(nazivpuni,8,5), mid(nazivpuni,37,4), mid(nazivpuni,14,4), mid(nazivpuni,22,5),Split([nazivpuni], '.')(7) & "." & Split([nazivpuni], '.')(8) & "." & Split([nazivpuni], '.')(9)
FROM bilansuspjeha
WHERE nazivpuni like "PL.*";

Let me know if this works for you.

HTH

 

[vojinb]

Thanks )
tried but it same thing-same error, I also already try it before, but result is same

 

[Guus2005]

Can you post a sample database describing the problem?

 

[vojinb]

Hi, here is db example.
I have two tables bilansuspjeha and kategorija. Into kategorija I insert data from bilansuspjeha with parts of the "nazivpuni" from bilansuspjeha.
Query insert into kategorija is adapted based on your suggestion

 

[Guus2005]

The problem was that the database engine which parses the queries couldn't handle split([tekstfield],".")(3)
The index of the array created by the split command was not recognised as such.

I have changed the name of the module from GetPart into modUtil. GetPart is the name of the function and in your case also the name of the module. Access couldn't figure out that you meant the function and not the module.

You *can* use the module name as a prefix for your function GetPart.GetPart(...), but that's a lot of typing and i don't like to type a lot.
If you do, you also have to know in which module each function is located.

If there is a function in modUtil named GetPart and you want to use that one, you can use modUtil.GetPart(...)
If there is only one function named GetPart, you can leave out the module name.

I have changed the GetPart function to return characters from a certain index to the end.
There is a also a new query, a copy from your insert query, which is slightly modified.

Let me know what you think.

Enjoy!

 

[vojinb]

Thanks )
I have to adopted it, but it's great.
Thanks once more