Home Ask Login Register

Developers Planet

Your answer is one click away!

Alex February 2016

Microsoft Access - Insert part of a string up until a specific character

I'll keep this simple:

I have three textboxes and a button. textbox1 is used as user input

button1 is pressed and this gives textbox2 and textbox3 a specific value based on the string from textbox1.

For example- User input: 'Hello & welcome everybody'

*Press button

textbox2.Value = 'Hello'

textbox3.Value = 'welcome'

So basically, how do I go about selecting part of a string up until a certain character?

And also, how do I go about selecting part of a string between a certain character/words (In this case, between '&' and 'everybody'

I know I could dim two variables as strings to store them, then set the value of the textboxes to equal the variables. But I don't know what code to use to obtain the specific parts of the string in the first place. Any ideas?


HansUp February 2016

Use the Split Function to split the string on the & character, returning an array of the resulting substring pieces.

Here is an example from the Immediate window:

strUserinput = "Hello & welcome everybody"
varPieces = Split(strUserinput, "&")
Debug.Print Trim(varPieces(0))
Debug.Print Trim(varPieces(1))
welcome everybody

Since you want just "welcome" instead of "welcome everybody", you can Split() again:

Debug.Print Split(Trim(varPieces(1)), " ")(0)

However it looks simpler to just Split() on the spaces in the first place and then take the first and third items from the array. (Thanks, Johnny Bones!):

varPieces = Split(strUserinput, " ")
? varPieces(0)
? varPieces(2)

Post Status

Asked in February 2016
Viewed 1,242 times
Voted 9
Answered 1 times


Leave an answer

Quote of the day: live life