We help IT Professionals succeed at work.

How extract characters up to the first ) mark and put them in a new field in a query

SteveL13
SteveL13 asked
on
In a query designer I am trying to figure out how to extract the characters up to the first ( mark and put them in a new field in the query.  So for example:

XYZ012 would return 012
and
ABC12 would return 12
Comment
Watch Question

President / Owner CARDA Consultants Inc.
Distinguished Expert 2018
Commented:
how to extract the characters up to the first ( mark
but you don't have ( in your examples?

But to answer the question you can use mid() to do this
Mid([YourFieldName], InStr([YourFieldName], "(")+1)

Open in new window

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:

or:


myText = Split(yourFieldname, "(")(0)


The split function returns an array, which you can declare and assign the return value of split to it, something like:


Dim myArray() as string

myArray = Split("now is the time for all good men", " ")


This would return an 8 element array starting at 0 and ending at 7 with each of the individual words.


But it's handy for your situation because if it doesn't find the left paren, it will just return the entire string as the first element (0).

Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
The Split() function, as explained by Dale, is another powerful tool, but do be aware it isn't available in queries natively.  That said, there is a simple workaround, see: https://www.devhut.net/2018/04/22/access-using-split-in-a-query/
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:

good catch, Daniel. Yes, i miss that "in a query" comment in the original post.