Remove text in brackets from query

Hi Ken,

Thanks for your help, but when I use this I get an error message saying that
I entered a function containing the wrong number of arguments. Any idea why?

I'm just going to reiterate what my problem is, as I think I haven't been
very clear up till now. I have several name fields in my query, and the one
I'm having problems with is called PatientRegistry.[GIVEN_NAMES] The data in
this field includes first name, middle name, and any other given names, but
also sometimes has comments in brackets. For example you might see: Janet
(NMN) or Janet (Elizabeth). They are round brackets, and I would like to
remove them along with whatever is within them. In other words I would like
all records to show in my query, but those records that have bracketed text
included in the GIVEN_NAMES field, to only show the text outside the
brackets. So instead of showing Janet (NMN) it would only show Janet.
Thanks.

Janet

"Ken Snell [MVP]" wrote:

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

Ofer,

I have something really strange happening. When I used the function you
gave here, it worked after I entered it and when I first ran the query, but
then when I closed the query and saved it, then reopened it only half the
amount of records were showing that were originally there. I don't
understand what is happening. Any idea? Thanks again for your help.

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

Can you post your SQL?
--
In God We Trust - Everything Else We Test


"JanetF" wrote:

Ofer,

I have something really strange happening. When I used the function you
gave here, it worked after I entered it and when I first ran the query, but
then when I closed the query and saved it, then reopened it only half the
amount of records were showing that were originally there. I don't
understand what is happening. Any idea? Thanks again for your help.

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

Those 'round brackets' are called parentheses. None of what went before will
work unless you substitute '(' for every '[' and ')' for every ']'.

I would assume that's why Ken's and Ofer's suggestions are not getting you
what you want.
--
Chaim


"JanetF" wrote:

Hi Ken,

Thanks for your help, but when I use this I get an error message saying that
I entered a function containing the wrong number of arguments. Any idea why?

I'm just going to reiterate what my problem is, as I think I haven't been
very clear up till now. I have several name fields in my query, and the one
I'm having problems with is called PatientRegistry.[GIVEN_NAMES] The data in
this field includes first name, middle name, and any other given names, but
also sometimes has comments in brackets. For example you might see: Janet
(NMN) or Janet (Elizabeth). They are round brackets, and I would like to
remove them along with whatever is within them. In other words I would like
all records to show in my query, but those records that have bracketed text
included in the GIVEN_NAMES field, to only show the text outside the
brackets. So instead of showing Janet (NMN) it would only show Janet.
Thanks.

Janet

"Ken Snell [MVP]" wrote:

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

Ofer,

I've changed the names of all the fields and tables, but here it is:

SELECT DISTINCT ClientRegistry.RECORD_STATUS, ClientRegistry.CLIENT_NUM,
ClientRegistry.CODE, ClientRegistry.LAST_NAME,
Replace(Replace([ClientRegistry].[GIVEN_NAMES],"(",""),")","") AS Expr1,
ClientRegistry.LAST_ENCOUNTER_DATE, ClientRegistry.ARRAY,
Encounter.CURR_ADDR_LINE_1, Encounter.CURR_ADDR_LINE_2,
Encounter.CURR_ADDR_CITY, Encounter.CURR_ADDR_PROVINCE,
Encounter.CURR_ADDR_POSTAL_CODE,
[Encounter].[CLIENT_HPHONE_AREA_CODE]+[Encounter].[CLIENT_HPHONE_NUMBER] AS
Phone, Encounter.DATE, EncounterHistory.DATE
FROM (ClientRegistry LEFT JOIN Encounter ON (ClientRegistry.CLIENT_NUM =
Encounter.CLIENT_NUM) AND (ClientRegistry.LAST_ENCOUNTER_DATE =
Encounter.DATE)) LEFT JOIN EncounterHistory ON (ClientRegistry.CLIENT_NUM =
EncounterHistory.CLIENT_NUM) AND (ClientRegistry.LAST_ENCOUNTER_DATE =
EncounterHistory.DATE)
WHERE (((ClientRegistry.RECORD_STATUS)="") AND
((ClientRegistry.LAST_ENCOUNTER_DATE)=#4/4/2002#) AND
((ClientRegistry.ARRAY)=0));

So it's the GIVEN_NAMES field where I only want to show the Given Names, not
any additional text in brackets. For example, in a Given Name field one
record may have: Janet (NMN). I only want to show Janet, not the (NMN).
Thanks.

Janet

"Ofer" wrote:

Can you post your SQL?
--
In God We Trust - Everything Else We Test


"JanetF" wrote:

Ofer,

I have something really strange happening. When I used the function you
gave here, it worked after I entered it and when I first ran the query, but
then when I closed the query and saved it, then reopened it only half the
amount of records were showing that were originally there. I don't
understand what is happening. Any idea? Thanks again for your help.

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

Actually I did that and still nothing works.

"Chaim" wrote:

Those 'round brackets' are called parentheses. None of what went before will
work unless you substitute '(' for every '[' and ')' for every ']'.

I would assume that's why Ken's and Ofer's suggestions are not getting you
what you want.
--
Chaim


"JanetF" wrote:

Hi Ken,

Thanks for your help, but when I use this I get an error message saying that
I entered a function containing the wrong number of arguments. Any idea why?

I'm just going to reiterate what my problem is, as I think I haven't been
very clear up till now. I have several name fields in my query, and the one
I'm having problems with is called PatientRegistry.[GIVEN_NAMES] The data in
this field includes first name, middle name, and any other given names, but
also sometimes has comments in brackets. For example you might see: Janet
(NMN) or Janet (Elizabeth). They are round brackets, and I would like to
remove them along with whatever is within them. In other words I would like
all records to show in my query, but those records that have bracketed text
included in the GIVEN_NAMES field, to only show the text outside the
brackets. So instead of showing Janet (NMN) it would only show Janet.
Thanks.

Janet

"Ken Snell [MVP]" wrote:

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

If you don't want the additional text in brackets, as you originally posted,
then try and use my first suggestion.
About the amount of records that been displayed, it got to do with the
filter and not with the fact the you changed the name.
Try and add that to the filter

WHERE (ClientRegistry.RECORD_STATUS="" OR ClientRegistry.RECORD_STATUS is
Null) AND
ClientRegistry.LAST_ENCOUNTER_DATE =#4/4/2002# AND
ClientRegistry.ARRAY=0

In any case, mybe one of the filers remove alot of the records
==========================================

--
In God We Trust - Everything Else We Test


"JanetF" wrote:

Ofer,

I've changed the names of all the fields and tables, but here it is:

SELECT DISTINCT ClientRegistry.RECORD_STATUS, ClientRegistry.CLIENT_NUM,
ClientRegistry.CODE, ClientRegistry.LAST_NAME,
Replace(Replace([ClientRegistry].[GIVEN_NAMES],"(",""),")","") AS Expr1,
ClientRegistry.LAST_ENCOUNTER_DATE, ClientRegistry.ARRAY,
Encounter.CURR_ADDR_LINE_1, Encounter.CURR_ADDR_LINE_2,
Encounter.CURR_ADDR_CITY, Encounter.CURR_ADDR_PROVINCE,
Encounter.CURR_ADDR_POSTAL_CODE,
[Encounter].[CLIENT_HPHONE_AREA_CODE]+[Encounter].[CLIENT_HPHONE_NUMBER] AS
Phone, Encounter.DATE, EncounterHistory.DATE
FROM (ClientRegistry LEFT JOIN Encounter ON (ClientRegistry.CLIENT_NUM =
Encounter.CLIENT_NUM) AND (ClientRegistry.LAST_ENCOUNTER_DATE =
Encounter.DATE)) LEFT JOIN EncounterHistory ON (ClientRegistry.CLIENT_NUM =
EncounterHistory.CLIENT_NUM) AND (ClientRegistry.LAST_ENCOUNTER_DATE =
EncounterHistory.DATE)
WHERE (((ClientRegistry.RECORD_STATUS)="") AND
((ClientRegistry.LAST_ENCOUNTER_DATE)=#4/4/2002#) AND
((ClientRegistry.ARRAY)=0));

So it's the GIVEN_NAMES field where I only want to show the Given Names, not
any additional text in brackets. For example, in a Given Name field one
record may have: Janet (NMN). I only want to show Janet, not the (NMN).
Thanks.

Janet

"Ofer" wrote:

Can you post your SQL?
--
In God We Trust - Everything Else We Test


"JanetF" wrote:

Ofer,

I have something really strange happening. When I used the function you
gave here, it worked after I entered it and when I first ran the query, but
then when I closed the query and saved it, then reopened it only half the
amount of records were showing that were originally there. I don't
understand what is happening. Any idea? Thanks again for your help.

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

I must assume that what you pasted/copied/typed into your query is not
exactly what I posted, then. You'll need to post the SQL statement of your
query (as Ofer requested) -- open the query in design view, click on Query
View icon on toolbar, select SQL, copy the statement that you see, and post
it here exactly as it is. Do not modify it in any way.

--

Ken Snell
MS ACCESS MVP

"JanetF" wrote in message
...
Hi Ken,

Thanks for your help, but when I use this I get an error message saying
that
I entered a function containing the wrong number of arguments. Any idea
why?

I'm just going to reiterate what my problem is, as I think I haven't been
very clear up till now. I have several name fields in my query, and the
one
I'm having problems with is called PatientRegistry.[GIVEN_NAMES] The data
in
this field includes first name, middle name, and any other given names,
but
also sometimes has comments in brackets. For example you might see:
Janet
(NMN) or Janet (Elizabeth). They are round brackets, and I would like to
remove them along with whatever is within them. In other words I would
like
all records to show in my query, but those records that have bracketed
text
included in the GIVEN_NAMES field, to only show the text outside the
brackets. So instead of showing Janet (NMN) it would only show Janet.
Thanks.

Janet

"Ken Snell [MVP]" wrote:

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