Split in VBA does not work

Asked

Viewed 615 times

2

I have the following code snippet:

CorpID = rsSQL.Fields("Name")
Tmp14 = Split(CorpID, , 1)
Tmp15 = Join(Tmp14)
Me.nomeUsuario.Caption = Tmp15

Where I want to find only the first name of the person, but returns the full name, ie the function split it’s not working, what I did wrong?

vba ms-access

  • What is the content of rsSQL.Fields("Name")?

  • A full name separated by spaces

  • The split is wrong for your purpose because you limited in 1 the number of strings obtained. Even correcting the split, you still have another problem: you are re-joining the name using the method join. See my answer.

2 answers

4


The problem is the third parameter.

For official documentation of the method, the third parameter is the maximum amount of substrings you want. If not reported, or if the default value (-1) is given, there is no limit to substrings to return. But in your case...

If you say you want a substring at most, no matter how many times the delimiter appears in the input. You will get only one string.

If you want to separate in name and surname, pass at least 2 in the third parameter. This transforms:

"Derpino Augusto de Oliveira Lima"

In:

"Derpino",  "Augusto de Oliveira Lima"

You can omit the third parameter to break the string in all spaces. And the default value for the second parameter, which is the delimiter, is space (" "), then you can omit it too. It stays like this:

Tmp14 = Split(CorpID)

Good luck!

  • Hello, I tried but get the full name :/

2

There are two problems. Considering the value "José Roberto" in rsSQL.Fields("Name"):

  • 1) In Split(CorpID, , 1), you limit the split to 1, so that ends up returning the full name, "José Roberto".

  • 2) Even if you correct for Tmp14 = Split(CorpID) in order to return Tmp14(0) = "José" and Tmp14(1) = "Roberto", in the next row you join them again: Tmp15 = Join(Tmp14). That is to say, Tmp15 contains again the full name "José Roberto".

The corrected code below will show only "José" in Me.nomeUsuario.Caption:

CorpID = rsSQL.Fields("Name")
Tmp14 = Split(CorpID)
'Tmp15 = Join(Tmp14)
Me.nomeUsuario.Caption = Tmp14(0)

I corrected the split ignored the join setting in the caption the first item of the split result; in this case, "José".

  • Good, that answer was more complete than mine :)

  • Solved my problem, thank you very much for the time.

  • @Sapires For nothing!

Browser other questions tagged vba ms-access

You are not signed in. Login or sign up in order to post.