If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Remove Certain String from Query Field
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 |
#2
|
|||
|
|||
Remove Certain String from Query Field
Try this --
Mid([YourField], InStrRev([YourField], "_")+1) -- Build a little, test a little. "Hadi" wrote: 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 |
#3
|
|||
|
|||
Remove Certain String from Query Field
On Wed, 25 Nov 2009 09:24:01 -0800, Hadi wrote:
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) -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail |
#4
|
|||
|
|||
Remove Certain String from Query Field
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? "KARL DEWEY" wrote: Try this -- Mid([YourField], InStrRev([YourField], "_")+1) -- Build a little, test a little. "Hadi" wrote: 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 |
#5
|
|||
|
|||
Remove Certain String from Query Field
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. "Hadi" wrote: 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? "KARL DEWEY" wrote: Try this -- Mid([YourField], InStrRev([YourField], "_")+1) -- Build a little, test a little. "Hadi" wrote: 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 |
#6
|
|||
|
|||
Remove Certain String from Query Field
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 wrote: 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 |
#7
|
|||
|
|||
Remove Certain String from Query Field
Works great. Thank you Karl
"KARL DEWEY" wrote: 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. "Hadi" wrote: 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? "KARL DEWEY" wrote: Try this -- Mid([YourField], InStrRev([YourField], "_")+1) -- Build a little, test a little. "Hadi" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|