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 said:
Can you post your SQL?
--
In God We Trust - Everything Else We Test
JanetF said:
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
:
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
:
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
:
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
:
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
:
Look at the Left, InStr, etc. string functions to do what you want.
--
Ken Snell
<MS ACCESS MVP>
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