Finding a substring from a variable string length using VB in MS Access

Question

I have an Access DB with a few tables, one of which has a column of address. These addresses all begin with a building name and are followed by city information. They are all separated by commas.

I am looking for a way in the VB code to take this field and separate it into two fields, with everything left of the first comma in one field and everything right of the first comma in the other.

I've tried variations on the following:

currentBuildingName = currentBuildingAddress.Substring(0, currentBuildingAddress.IndexOf(","))
currentCity = currentBuildingAddress.Substring(currentBuildingAddress.IndexOf(","), 1)

But with no success. I've also tried looking at the left() and right() functions, but they require you to specify the integer location of where the split should occur, which is variable.

Any advice folks? How can I split a string based on the first comma, with everything to the left in one field and everything to the right in another field, in MS Access 2010?

Gord Thompson · Accepted Answer · 2013-11-15 12:14:52Z

3

Use the InStr() function, InStr("expression_to_search", "search_string"). It returns the starting position of the string you're searching for or zero if it doesn't find it. Once you've got the position of the comma you can use Right() and Left() to parse the string. For more details see

InStr Function.

edited Nov 15, 2013 at 12:14

Gord Thompson

125k39 gold badges252 silver badges458 bronze badges

answered Nov 15, 2013 at 11:56

OTTA

1,0797 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andrew Martin Over a year ago

Yeah this is the way to go. I'll mark this as accepted, but post my end result as a separate answer. Thanks

Andrew Martin · Accepted Answer · 2013-11-15 12:21:31Z

0

Thanks to @OTTA for the guidance. This was my final answer:

startingPosition = InStr(currentBuildingAddress, ",")

currentBuildingName = Left(currentBuildingAddress, (startingPosition))
currentCity = Right(currentBuildingAddress, Len(currentBuildingAddress) - startingPosition)

Nice and simple.

answered Nov 15, 2013 at 12:21

Andrew Martin

5,78111 gold badges60 silver badges94 bronze badges

Collectives™ on Stack Overflow

Finding a substring from a variable string length using VB in MS Access

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related