Remove Certain String from Query Field

[Hadi]

Hello Experts,

I have a query with mulitple fields. one of them contains the names of the
projects our depatrment is working on. the name starts with a 4 digit number,
then an underscore, then a 4 or 5 digit number then another underscore then
the Name of the proejct itself. so the field looks like this
xxxx_xxxx_Project Name. I want to create an experssion that only shows the
Project Name part without the numbers or the underscores. the Problem i'm
having tis that the number of these digits vary from project to project. the
total number of characters can very between 10 12 so I can really use left,
mid or some of these other functions. Is there another way to do this?

thank you,

Hadi

 

[KARL DEWEY]

Try this --
Mid([YourField], InStrRev([YourField], "_")+1)

 

[fredg]

Hello Experts,

I have a query with mulitple fields. one of them contains the names of the
projects our depatrment is working on. the name starts with a 4 digit number,
then an underscore, then a 4 or 5 digit number then another underscore then
the Name of the proejct itself. so the field looks like this
xxxx_xxxx_Project Name. I want to create an experssion that only shows the
Project Name part without the numbers or the underscores. the Problem i'm
having tis that the number of these digits vary from project to project. the
total number of characters can very between 10 12 so I can really use left,
mid or some of these other functions. Is there another way to do this?

thank you,

Hadi

Note: In the future, when posting to these newsgroups, it would be in
your best interest to include your Access version number. It may make
a difference in the response. For example, what may work in Access
2007 may not work in previous versions, and vise versa.

if your version of Access has the InStrRev() function you can use:

ProjName:Mid([FieldName],InStrRev([FieldName],"_")+1)

 

[Hadi]

Karl,

this works except that Project Name also might an underscore somewhere in
it. is there a way to pick up everything to the left of the SECOND underscore?

Try this --
Mid([YourField], InStrRev([YourField], "_")+1)

--
Build a little, test a little.

Hello Experts,

I have a query with mulitple fields. one of them contains the names of the
projects our depatrment is working on. the name starts with a 4 digit number,
then an underscore, then a 4 or 5 digit number then another underscore then
the Name of the proejct itself. so the field looks like this
xxxx_xxxx_Project Name. I want to create an experssion that only shows the
Project Name part without the numbers or the underscores. the Problem i'm
having tis that the number of these digits vary from project to project. the
total number of characters can very between 10 12 so I can really use left,
mid or some of these other functions. Is there another way to do this?

thank you,

Hadi

 

[KARL DEWEY]

You can nest it. Try this --
Mid(Mid([YourField], InStr([YourField], "_")+1), InStr(Mid([YourField],
InStr([YourField], "_")+1), "_")+1)

--
Build a little, test a little.

Karl,

this works except that Project Name also might an underscore somewhere in
it. is there a way to pick up everything to the left of the SECOND underscore?

Try this --
Mid([YourField], InStrRev([YourField], "_")+1)

--
Build a little, test a little.

Hello Experts,

I have a query with mulitple fields. one of them contains the names of the
projects our depatrment is working on. the name starts with a 4 digit number,
then an underscore, then a 4 or 5 digit number then another underscore then
the Name of the proejct itself. so the field looks like this
xxxx_xxxx_Project Name. I want to create an experssion that only shows the
Project Name part without the numbers or the underscores. the Problem i'm
having tis that the number of these digits vary from project to project. the
total number of characters can very between 10 12 so I can really use left,
mid or some of these other functions. Is there another way to do this?

thank you,

Hadi

 

[John Spencer]

ASSUMPTION: There is ALWAYS two underscores.

Mid(ProjectField,Instr(Instr(1,ProjectField,"_")+1,ProjectField,"_")+1)

You can test before you execute the expression

IIF(ProjectField Like "*_*_*",
Mid(ProjectField,Instr(Instr(1,ProjectField,"_")+1,ProjectField,"_")+1)
,ProjectField)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Hadi]

Works great. Thank you Karl

You can nest it. Try this --
Mid(Mid([YourField], InStr([YourField], "_")+1), InStr(Mid([YourField],
InStr([YourField], "_")+1), "_")+1)

--
Build a little, test a little.

Karl,

this works except that Project Name also might an underscore somewhere in
it. is there a way to pick up everything to the left of the SECOND underscore?

Try this --
Mid([YourField], InStrRev([YourField], "_")+1)

--
Build a little, test a little.


:

Hello Experts,

I have a query with mulitple fields. one of them contains the names of the
projects our depatrment is working on. the name starts with a 4 digit number,
then an underscore, then a 4 or 5 digit number then another underscore then
the Name of the proejct itself. so the field looks like this
xxxx_xxxx_Project Name. I want to create an experssion that only shows the
Project Name part without the numbers or the underscores. the Problem i'm
having tis that the number of these digits vary from project to project. the
total number of characters can very between 10 12 so I can really use left,
mid or some of these other functions. Is there another way to do this?

thank you,

Hadi