split field

[kolait]

Hello
I have a field like the one below that I want to divide into three fields. Thanks for helping me. my office is 2019
25/12/1
7/9/15
78/12/21

 

[Gasman]

Use the Split function with a "/" delimiter.?

https://trumpexcel.com/vba-split-function/

 

[Minty]

I would probably use the split function.

Split("25/12/1" ,"/") 
Result "25","12","1"

More here https://www.techonthenet.com/access/functions/string/split.php

 

[theDBguy]

Hi kolait. If you're trying to do this in a query, as the forum title implies, then; unfortunately, you won't be able to use the Split() function directly. If you only have three elements to split, then you might be able to use a combination of the Left(), Mid(), and InStr() functions. Otherwise, you will have to create a custom function to wrap the Split() function in that you can then use in your query.

 

[kolait]

Use the Split function with a "/" delimiter.?

https://trumpexcel.com/vba-split-function/

Thank you..but this function does not exist in my office
my office is 2019

 

[kolait]

I would probably use the split function.

Split("25/12/1" ,"/") 
Result "25","12","1"

More here https://www.techonthenet.com/access/functions/string/split.php

Thank you..but this function does not exist in my office
my office is 2019

 

[kolait]

Hi kolait. If you're trying to do this in a query, as the forum title implies, then; unfortunately, you won't be able to use the Split() function directly. If you only have three elements to split, then you might be able to use a combination of the Left(), Mid(), and InStr() functions. Otherwise, you will have to create a custom function to wrap the Split() function in that you can use in your query.

thank you.
I just got this out. I couldn't do it for the rest
Left([feild],InStr([feild,"/")-1)

 

[isladogs]

It does exist in all versions of Office including 2019/365.
However its VBA only. it can't be used directly in a query (unless called from another function).

 

[theDBguy]

thank you.
I just got this out. I couldn't do it for the rest
Left([feild],InStr([feild,"/")-1)

Try this one:

Mid([field],InStr([field],"/")+1,InStr(Mid([field],InStr([field],"/")+1),"/")-1)

(untested)

 

[kolait]

Try this one:

Mid([field],InStr([field],"/")+1,InStr(Mid([field],InStr([field],"/")+1),"/")-1)

(untested)

thank you so much
What to do for the right?

 

[kolait]

It does exist in all versions of Office including 2019/365.
However its VBA only. it can't be used directly in a query (unless called from another function).

thank you
I wish they could be used directly in the query

 

[isladogs]

thank you
I wish they could be used directly in the query

So do we all. But I have good news for you
See https://www.devhut.net/2018/04/22/access-using-split-in-a-query/

 

[theDBguy]

thank you so much
What to do for the right?

Hi. I'm surprised that worked without testing. For the right, it's much easier.

Mid([field],InStrRev([field],"/")+1)

 

[Gasman]

So do we all. But I have good news for you
See https://www.devhut.net/2018/04/22/access-using-split-in-a-query/

Nice. I was going to suggest something along those lines, but as it has already been done, much better.

@kolait
Sorry, I never realised this was for a query.

 

[kolait]

Hi. I'm surprised that worked without testing. For the right, it's much easier.

Mid([field],InStrRev([field],"/")+1)

thank you so much..
It was great..

 

[kolait]

So do we all. But I have good news for you
See https://www.devhut.net/2018/04/22/access-using-split-in-a-query/

I tried this but it made an error

 

[theDBguy]

thank you so much..
It was great..

Hi. You're welcome. We were all happy to assist. Good luck with your project.

 

[isladogs]

I tried this but it made an error

It does work. Perhaps you used it incorrectly.
Even though you have a solution, I would recommend trying it again as it is so useful.
If you still get an error please provide details / screenshots

 

[kolait]

It does work. Perhaps you used it incorrectly.
Even though you have a solution, I would recommend trying it again as it is so useful.
If you still get an error please provide details / screenshots

thank you
I managed to do it.
You and many other friends have helped me a lot. Thank you all

 

[theDBguy]

I usually point people to this one. It does the same thing.