extract part of a field

[Guest]

how do I extract part of a field after the "/" character in a query?

 

[PC Datasheet]

Look at the Left, Mid and Right functions in the Help file.

 

[Guest]

Unless your fields are fixed length, and the "/" is always in the same spot,
you will likely also need the len() and instr() functions. Such as:

Right([YourField],len([YourField])-instr([YourField],"/",1))

HTH, Ted Allen

 

[Jamie Collins]

Unless your fields are fixed length, and the "/" is always in the same spot,
you will likely also need the len() and instr() functions. Such as:

Right([YourField],len([YourField])-instr([YourField],"/",1))

You could use just MID and INSTR e.g.

MID(MyTextCol,INSTR(1,MyTextCol,'/',1)+1)

Note the '1' used as the first argument to the INSTR function. Without
this your expression would error outside of the MS Access UI.

Jamie.

--

 

[Guest]

You're right Jamie, much cleaner solution. I forgot that the mid function
defaults the length to the remainder of the string if ommitted. Also, good
point about the start pos in the Instr() function. I guess I only use it in
Access environment so I hadn't come across that issue.

-Ted Allen

Unless your fields are fixed length, and the "/" is always in the same spot,
you will likely also need the len() and instr() functions. Such as:

Right([YourField],len([YourField])-instr([YourField],"/",1))

You could use just MID and INSTR e.g.

MID(MyTextCol,INSTR(1,MyTextCol,'/',1)+1)

Note the '1' used as the first argument to the INSTR function. Without
this your expression would error outside of the MS Access UI.

Jamie.

 

[Guest]

Hello, I used Jamie's solution successfully, however, I'm wondering if
there's a way to extract between to characters. The data I want to extract
will always fall between "-" and ":". Right now I'm picking up everything
after "-".

Thanks,
Mary

You're right Jamie, much cleaner solution. I forgot that the mid function
defaults the length to the remainder of the string if ommitted. Also, good
point about the start pos in the Instr() function. I guess I only use it in
Access environment so I hadn't come across that issue.

-Ted Allen

Unless your fields are fixed length, and the "/" is always in the same spot,
you will likely also need the len() and instr() functions. Such as:

Right([YourField],len([YourField])-instr([YourField],"/",1))

You could use just MID and INSTR e.g.

MID(MyTextCol,INSTR(1,MyTextCol,'/',1)+1)

Note the '1' used as the first argument to the INSTR function. Without
this your expression would error outside of the MS Access UI.

Jamie.

 

[Ken Snell [MVP]]

Something like this, perhaps:

ExtractedString = Mid(Left(OriginalString, InStrRev(OriginalString, ":") -
1), InStr(OriginalString, "-") + 1)

--

Ken Snell
<MS ACCESS MVP>

Hello, I used Jamie's solution successfully, however, I'm wondering if
there's a way to extract between to characters. The data I want to
extract
will always fall between "-" and ":". Right now I'm picking up everything
after "-".

Thanks,
Mary

You're right Jamie, much cleaner solution. I forgot that the mid
function
defaults the length to the remainder of the string if ommitted. Also,
good
point about the start pos in the Instr() function. I guess I only use it
in
Access environment so I hadn't come across that issue.

-Ted Allen

Unless your fields are fixed length, and the "/" is always in the
same spot,
you will likely also need the len() and instr() functions. Such as:

Right([YourField],len([YourField])-instr([YourField],"/",1))


You could use just MID and INSTR e.g.

MID(MyTextCol,INSTR(1,MyTextCol,'/',1)+1)

Note the '1' used as the first argument to the INSTR function. Without
this your expression would error outside of the MS Access UI.

Jamie.

 

[Guest]

Ken, I'm getting an "Undefined function, InStrRev" error, this is what I tried:

DESCRIPTION: Mid(Left([DESCR],InStrRev([DESCR],":")-1),InStr([DESCR],"-")+1)

Thanks, Mary

Something like this, perhaps:

ExtractedString = Mid(Left(OriginalString, InStrRev(OriginalString, ":") -
1), InStr(OriginalString, "-") + 1)

--

Ken Snell
<MS ACCESS MVP>

Hello, I used Jamie's solution successfully, however, I'm wondering if
there's a way to extract between to characters. The data I want to
extract
will always fall between "-" and ":". Right now I'm picking up everything
after "-".

Thanks,
Mary

You're right Jamie, much cleaner solution. I forgot that the mid
function
defaults the length to the remainder of the string if ommitted. Also,
good
point about the start pos in the Instr() function. I guess I only use it
in
Access environment so I hadn't come across that issue.

-Ted Allen

:


Unless your fields are fixed length, and the "/" is always in the
same spot,
you will likely also need the len() and instr() functions. Such as:

Right([YourField],len([YourField])-instr([YourField],"/",1))


You could use just MID and INSTR e.g.

MID(MyTextCol,INSTR(1,MyTextCol,'/',1)+1)

Note the '1' used as the first argument to the INSTR function. Without
this your expression would error outside of the MS Access UI.

Jamie.

 

[Ken Snell [MVP]]

Which version of ACCESS are you using?

--

Ken Snell
<MS ACCESS MVP>

Ken, I'm getting an "Undefined function, InStrRev" error, this is what I
tried:

DESCRIPTION:
Mid(Left([DESCR],InStrRev([DESCR],":")-1),InStr([DESCR],"-")+1)

Thanks, Mary

Something like this, perhaps:

ExtractedString = Mid(Left(OriginalString, InStrRev(OriginalString,
":") -
1), InStr(OriginalString, "-") + 1)

--

Ken Snell
<MS ACCESS MVP>

Hello, I used Jamie's solution successfully, however, I'm wondering if
there's a way to extract between to characters. The data I want to
extract
will always fall between "-" and ":". Right now I'm picking up
everything
after "-".

Thanks,
Mary

:

You're right Jamie, much cleaner solution. I forgot that the mid
function
defaults the length to the remainder of the string if ommitted. Also,
good
point about the start pos in the Instr() function. I guess I only use
it
in
Access environment so I hadn't come across that issue.

-Ted Allen

:


Unless your fields are fixed length, and the "/" is always in the
same spot,
you will likely also need the len() and instr() functions. Such
as:

Right([YourField],len([YourField])-instr([YourField],"/",1))


You could use just MID and INSTR e.g.

MID(MyTextCol,INSTR(1,MyTextCol,'/',1)+1)

Note the '1' used as the first argument to the INSTR function.
Without
this your expression would error outside of the MS Access UI.

Jamie.

 

[Guest]

Access 97

Which version of ACCESS are you using?

--

Ken Snell
<MS ACCESS MVP>

Ken, I'm getting an "Undefined function, InStrRev" error, this is what I
tried:

DESCRIPTION:
Mid(Left([DESCR],InStrRev([DESCR],":")-1),InStr([DESCR],"-")+1)

Thanks, Mary

Something like this, perhaps:

ExtractedString = Mid(Left(OriginalString, InStrRev(OriginalString,
":") -
1), InStr(OriginalString, "-") + 1)

--

Ken Snell
<MS ACCESS MVP>

Hello, I used Jamie's solution successfully, however, I'm wondering if
there's a way to extract between to characters. The data I want to
extract
will always fall between "-" and ":". Right now I'm picking up
everything
after "-".

Thanks,
Mary

:

You're right Jamie, much cleaner solution. I forgot that the mid
function
defaults the length to the remainder of the string if ommitted. Also,
good
point about the start pos in the Instr() function. I guess I only use
it
in
Access environment so I hadn't come across that issue.

-Ted Allen

:


Unless your fields are fixed length, and the "/" is always in the
same spot,
you will likely also need the len() and instr() functions. Such
as:

Right([YourField],len([YourField])-instr([YourField],"/",1))


You could use just MID and INSTR e.g.

MID(MyTextCol,INSTR(1,MyTextCol,'/',1)+1)

Note the '1' used as the first argument to the INSTR function.
Without
this your expression would error outside of the MS Access UI.

Jamie.

 

[Ken Snell [MVP]]

ahhh......ACCESS 97 doesn't contain that built-in function. So let's change
the expression, assuming that there is only the one : character in the
string:

ExtractedString = Mid(Left(OriginalString, InStr(OriginalString, ":") - 1),
InStr(OriginalString, "-") + 1)

--

Ken Snell
<MS ACCESS MVP>

Access 97

Which version of ACCESS are you using?

--

Ken Snell
<MS ACCESS MVP>

Ken, I'm getting an "Undefined function, InStrRev" error, this is what
I
tried:

DESCRIPTION:
Mid(Left([DESCR],InStrRev([DESCR],":")-1),InStr([DESCR],"-")+1)

Thanks, Mary

:

Something like this, perhaps:

ExtractedString = Mid(Left(OriginalString, InStrRev(OriginalString,
":") -
1), InStr(OriginalString, "-") + 1)

--

Ken Snell
<MS ACCESS MVP>

Hello, I used Jamie's solution successfully, however, I'm wondering
if
there's a way to extract between to characters. The data I want to
extract
will always fall between "-" and ":". Right now I'm picking up
everything
after "-".

Thanks,
Mary

:

You're right Jamie, much cleaner solution. I forgot that the mid
function
defaults the length to the remainder of the string if ommitted.
Also,
good
point about the start pos in the Instr() function. I guess I only
use
it
in
Access environment so I hadn't come across that issue.

-Ted Allen

:


Unless your fields are fixed length, and the "/" is always in
the
same spot,
you will likely also need the len() and instr() functions.
Such
as:

Right([YourField],len([YourField])-instr([YourField],"/",1))


You could use just MID and INSTR e.g.

MID(MyTextCol,INSTR(1,MyTextCol,'/',1)+1)

Note the '1' used as the first argument to the INSTR function.
Without
this your expression would error outside of the MS Access UI.

Jamie.

 

[Guest]

Perfect! Thanks so much for your help!
Mary

ahhh......ACCESS 97 doesn't contain that built-in function. So let's change
the expression, assuming that there is only the one : character in the
string:

ExtractedString = Mid(Left(OriginalString, InStr(OriginalString, ":") - 1),
InStr(OriginalString, "-") + 1)

--

Ken Snell
<MS ACCESS MVP>

Access 97

Which version of ACCESS are you using?

--

Ken Snell
<MS ACCESS MVP>

Ken, I'm getting an "Undefined function, InStrRev" error, this is what
I
tried:

DESCRIPTION:
Mid(Left([DESCR],InStrRev([DESCR],":")-1),InStr([DESCR],"-")+1)

Thanks, Mary

:

Something like this, perhaps:

ExtractedString = Mid(Left(OriginalString, InStrRev(OriginalString,
":") -
1), InStr(OriginalString, "-") + 1)

--

Ken Snell
<MS ACCESS MVP>

Hello, I used Jamie's solution successfully, however, I'm wondering
if
there's a way to extract between to characters. The data I want to
extract
will always fall between "-" and ":". Right now I'm picking up
everything
after "-".

Thanks,
Mary

:

You're right Jamie, much cleaner solution. I forgot that the mid
function
defaults the length to the remainder of the string if ommitted.
Also,
good
point about the start pos in the Instr() function. I guess I only
use
it
in
Access environment so I hadn't come across that issue.

-Ted Allen

:


Unless your fields are fixed length, and the "/" is always in
the
same spot,
you will likely also need the len() and instr() functions.
Such
as:

Right([YourField],len([YourField])-instr([YourField],"/",1))


You could use just MID and INSTR e.g.

MID(MyTextCol,INSTR(1,MyTextCol,'/',1)+1)

Note the '1' used as the first argument to the INSTR function.
Without
this your expression would error outside of the MS Access UI.

Jamie.

 

[Ken Snell [MVP]]

You're welcome.

 

[Guest]

Hello, can I ask for one more variation? I'd like to pull the 3rd character
after the /. I'm getting the 3rd character plus everything that follows. I
only need that character.

Here's what I've got.

Special: Mid([Circuit_ID],InStr(3,[Circuit_ID],'/',3)+3)

How do I cut it off so nothing else displays but the 3rd character after the
/.

Thanks!
Mary

 

[Ken Snell [MVP]]

There is a third argument for the Mid function that tells the function how
many characters to select. So, if you just want one character:

Special: Mid([Circuit_ID],InStr(3,[Circuit_ID],'/',3)+3, 1)

--

Ken Snell
<MS ACCESS MVP>

Hello, can I ask for one more variation? I'd like to pull the 3rd
character
after the /. I'm getting the 3rd character plus everything that follows.
I
only need that character.

Here's what I've got.

Special: Mid([Circuit_ID],InStr(3,[Circuit_ID],'/',3)+3)

How do I cut it off so nothing else displays but the 3rd character after
the
/.

Thanks!
Mary

You're welcome.

 

[Guest]

Hello again -

I have been using the format from Ken successfully until a new twist came up
today. Is there a way to make this an If/then statement.

Here is my current statement:
DESCR:
LTrim(Mid(Left([Description],InStr([Description],"(")-1),InStr([Description],":")+1))

I need to see everything after the : and before the (
In today's case, there is no ( so I just need to see everything after the :

I've used IIF statements, but not sure how to incorporate one into this
statement.

Thanks again!
Mary

ahhh......ACCESS 97 doesn't contain that built-in function. So let's change
the expression, assuming that there is only the one : character in the
string:

ExtractedString = Mid(Left(OriginalString, InStr(OriginalString, ":") - 1),
InStr(OriginalString, "-") + 1)

--

Ken Snell
<MS ACCESS MVP>

Access 97

Which version of ACCESS are you using?

--

Ken Snell
<MS ACCESS MVP>

Ken, I'm getting an "Undefined function, InStrRev" error, this is what
I
tried:

DESCRIPTION:
Mid(Left([DESCR],InStrRev([DESCR],":")-1),InStr([DESCR],"-")+1)

Thanks, Mary

:

Something like this, perhaps:

ExtractedString = Mid(Left(OriginalString, InStrRev(OriginalString,
":") -
1), InStr(OriginalString, "-") + 1)

--

Ken Snell
<MS ACCESS MVP>

Hello, I used Jamie's solution successfully, however, I'm wondering
if
there's a way to extract between to characters. The data I want to
extract
will always fall between "-" and ":". Right now I'm picking up
everything
after "-".

Thanks,
Mary

:

You're right Jamie, much cleaner solution. I forgot that the mid
function
defaults the length to the remainder of the string if ommitted.
Also,
good
point about the start pos in the Instr() function. I guess I only
use
it
in
Access environment so I hadn't come across that issue.

-Ted Allen

:


Unless your fields are fixed length, and the "/" is always in
the
same spot,
you will likely also need the len() and instr() functions.
Such
as:

Right([YourField],len([YourField])-instr([YourField],"/",1))


You could use just MID and INSTR e.g.

MID(MyTextCol,INSTR(1,MyTextCol,'/',1)+1)

Note the '1' used as the first argument to the INSTR function.
Without
this your expression would error outside of the MS Access UI.

Jamie.