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?

Answers


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))
Hello
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)
welcome

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)
Hello
? varPieces(2)
welcome

Post Status

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

Search




Leave an answer