|
| 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
August 15th, 2005, 05:47 PM
|
|||
|
|||
Remove text in brackets from query
Hello,
I have a first name field in a query that has text in brackets after the first name. I would like to remove the brackets and the text within the brackets. How do I go about doing this? Thanks in advance. Janet 
|
|
#2
August 15th, 2005, 05:59 PM
|
|||
|
|||
|
Look at the Left, InStr, etc. string functions to do what you want.
-- Ken Snell MS ACCESS MVP "JanetF" wrote in message ... Hello, I have a first name field in a query that has text in brackets after the first name. I would like to remove the brackets and the text within the brackets. How do I go about doing this? Thanks in advance. Janet
|
|
#3
August 15th, 2005, 06:27 PM
|
|||
|
|||
|
Thanks, but I need a bit more information than that. The text in brackets is
sometimes there and sometimes not. It can be any imaginable length. I need to know how to not include anything in brackets in the query results. Thanks. Janet "Ken Snell [MVP]" wrote: Look at the Left, InStr, etc. string functions to do what you want. -- Ken Snell MS ACCESS MVP "JanetF" wrote in message ... Hello, I have a first name field in a query that has text in brackets after the first name. I would like to remove the brackets and the text within the brackets. How do I go about doing this? Thanks in advance. Janet
|
|
#4
August 15th, 2005, 06:35 PM
|
|||
|
|||
|
You can try this
IIF(instr(FieldName,"[")=true,left(FieldName,instr(FieldName,"[")-1) & mid(FieldName,instr(FieldName,"]")+1), FieldName) -- In God We Trust - Everything Else We Test "JanetF" wrote: Thanks, but I need a bit more information than that. The text in brackets is sometimes there and sometimes not. It can be any imaginable length. I need to know how to not include anything in brackets in the query results. Thanks. Janet "Ken Snell [MVP]" wrote: Look at the Left, InStr, etc. string functions to do what you want. -- Ken Snell MS ACCESS MVP "JanetF" wrote in message ... Hello, I have a first name field in a query that has text in brackets after the first name. I would like to remove the brackets and the text within the brackets. How do I go about doing this? Thanks in advance. Janet
|
|
#5
August 15th, 2005, 07:29 PM
|
|||
|
|||
|
Thank you so much Ofer!! That worked! I am so grateful.
Janet "JanetF" wrote: Hello, I have a first name field in a query that has text in brackets after the first name. I would like to remove the brackets and the text within the brackets. How do I go about doing this? Thanks in advance. Janet
|
|
#6
August 15th, 2005, 07:34 PM
|
|||
|
|||
|
Ofer,
I just thanked you for this and said it worked, but after looking more closely at my query results, I realized that what happened was all records that included bracketed text in the "GIVEN_NAMES" field are now not showing. I need them to show, just without the bracketed text included. Hope you can help. Thanks. Janet "Ofer" wrote: You can try this IIF(instr(FieldName,"[")=true,left(FieldName,instr(FieldName,"[")-1) & mid(FieldName,instr(FieldName,"]")+1), FieldName) -- In God We Trust - Everything Else We Test "JanetF" wrote: Thanks, but I need a bit more information than that. The text in brackets is sometimes there and sometimes not. It can be any imaginable length. I need to know how to not include anything in brackets in the query results. Thanks. Janet "Ken Snell [MVP]" wrote: Look at the Left, InStr, etc. string functions to do what you want. -- Ken Snell MS ACCESS MVP "JanetF" wrote in message ... Hello, I have a first name field in a query that has text in brackets after the first name. I would like to remove the brackets and the text within the brackets. How do I go about doing this? Thanks in advance. Janet
|
|
#7
August 15th, 2005, 07:41 PM
|
|||
|
|||
|
In the first post you asked for
would like to remove the brackets and the text within the brackets In that case try and use the replace function to remove the brackets Replace(Replace(FieldName,"[",""),"]","") I hoped that helped -- In God We Trust - Everything Else We Test "JanetF" wrote: Ofer, I just thanked you for this and said it worked, but after looking more closely at my query results, I realized that what happened was all records that included bracketed text in the "GIVEN_NAMES" field are now not showing. I need them to show, just without the bracketed text included. Hope you can help. Thanks. Janet "Ofer" wrote: You can try this IIF(instr(FieldName,"[")=true,left(FieldName,instr(FieldName,"[")-1) & mid(FieldName,instr(FieldName,"]")+1), FieldName) -- In God We Trust - Everything Else We Test "JanetF" wrote: Thanks, but I need a bit more information than that. The text in brackets is sometimes there and sometimes not. It can be any imaginable length. I need to know how to not include anything in brackets in the query results. Thanks. Janet "Ken Snell [MVP]" wrote: Look at the Left, InStr, etc. string functions to do what you want. -- Ken Snell MS ACCESS MVP "JanetF" wrote in message ... Hello, I have a first name field in a query that has text in brackets after the first name. I would like to remove the brackets and the text within the brackets. How do I go about doing this? Thanks in advance. Janet
|
|
#8
August 15th, 2005, 08:08 PM
|
|||
|
|||
|
Giving a completely generic answer sometimes is misleading, especially when
one learns (as you have said here) that the original question was not complete. Something such as this: MyName: Left([FirstName], IIf(InStr([FirstName], "[")=0, Len([FirstName], InStr([FirstName], "[")-1) -- Ken Snell MS ACCESS MVP "JanetF" wrote in message ... Thanks, but I need a bit more information than that. The text in brackets is sometimes there and sometimes not. It can be any imaginable length. I need to know how to not include anything in brackets in the query results. Thanks. Janet "Ken Snell [MVP]" wrote: Look at the Left, InStr, etc. string functions to do what you want. -- Ken Snell MS ACCESS MVP "JanetF" wrote in message ... Hello, I have a first name field in a query that has text in brackets after the first name. I would like to remove the brackets and the text within the brackets. How do I go about doing this? Thanks in advance. Janet
|
|
#9
August 15th, 2005, 08:25 PM
|
|||
|
|||
|
Sorry if I wasn't clear. This one worked. Thanks so much.
Janet "Ofer" wrote: In the first post you asked for would like to remove the brackets and the text within the brackets In that case try and use the replace function to remove the brackets Replace(Replace(FieldName,"[",""),"]","") I hoped that helped -- In God We Trust - Everything Else We Test "JanetF" wrote: Ofer, I just thanked you for this and said it worked, but after looking more closely at my query results, I realized that what happened was all records that included bracketed text in the "GIVEN_NAMES" field are now not showing. I need them to show, just without the bracketed text included. Hope you can help. Thanks. Janet "Ofer" wrote: You can try this IIF(instr(FieldName,"[")=true,left(FieldName,instr(FieldName,"[")-1) & mid(FieldName,instr(FieldName,"]")+1), FieldName) -- In God We Trust - Everything Else We Test "JanetF" wrote: Thanks, but I need a bit more information than that. The text in brackets is sometimes there and sometimes not. It can be any imaginable length. I need to know how to not include anything in brackets in the query results. Thanks. Janet "Ken Snell [MVP]" wrote: Look at the Left, InStr, etc. string functions to do what you want. -- Ken Snell MS ACCESS MVP "JanetF" wrote in message ... Hello, I have a first name field in a query that has text in brackets after the first name. I would like to remove the brackets and the text within the brackets. How do I go about doing this? Thanks in advance. Janet
|
|
#10
August 15th, 2005, 09:15 PM
|
|||
|
|||
|
Sorry...typo:
MyName: Left([FirstName], IIf(InStr([FirstName], "[")=0, Len([FirstName]), InStr([FirstName], "[")-1)) -- Ken Snell MS ACCESS MVP "Ken Snell [MVP]" wrote in message ... Giving a completely generic answer sometimes is misleading, especially when one learns (as you have said here) that the original question was not complete. Something such as this: MyName: Left([FirstName], IIf(InStr([FirstName], "[")=0, Len([FirstName], InStr([FirstName], "[")-1) -- Ken Snell MS ACCESS MVP "JanetF" wrote in message ... Thanks, but I need a bit more information than that. The text in brackets is sometimes there and sometimes not. It can be any imaginable length. I need to know how to not include anything in brackets in the query results. Thanks. Janet "Ken Snell [MVP]" wrote: Look at the Left, InStr, etc. string functions to do what you want. -- Ken Snell MS ACCESS MVP "JanetF" wrote in message ... Hello, I have a first name field in a query that has text in brackets after the first name. I would like to remove the brackets and the text within the brackets. How do I go about doing this? Thanks in advance. Janet
|
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Need Subforms? | AccessRookie | Using Forms | 7 | April 8th, 2005 09:30 AM |
| Outline | Renee Hendershott | Page Layout | 2 | December 25th, 2004 02:49 PM |
| Complex Query (was: Refresh vs. requery vs. Remove Filter in FormsCoding NG) | rgrantz | Running & Setting Up Queries | 1 | November 23rd, 2004 01:59 AM |
| Union Query Not Returning A Value | Jeff G | Running & Setting Up Queries | 2 | October 19th, 2004 05:47 PM |
| Extract Text Outside of Brackets | Frank Kabel | Worksheet Functions | 2 | February 11th, 2004 07:38 AM |
Linear Mode
