Please help - returning strings

[Guest]

I have a query that returns text. SOME text is just text but some text has a
number like X1027 - in front of the text. It might read like "X1027 -
Jonesboro High School". The text lengths vary from school to school and some
don't have the numbers in front. How can I remove the numbers, spaces, and -
in front of the ones that do and leave alone the ones that don't. Help!!!
Thanks so much!!!!!!

 

[Carl Rapson]

I have a query that returns text. SOME text is just text but some text has
a
number like X1027 - in front of the text. It might read like "X1027 -
Jonesboro High School". The text lengths vary from school to school and
some
don't have the numbers in front. How can I remove the numbers, spaces,
and -
in front of the ones that do and leave alone the ones that don't. Help!!!
Thanks so much!!!!!!

Do the ones with the extra text always include the " - "? If so, you could
do something like

IIf((InStr(text,"-")>0),Right(text,
(Len(text)-(InStr(text,"-")+1))),text)

Of course, that also assumes that there is no "-" in any of the school
names.

Carl Rapson

 

[Guest]

Thanks so much for helping me. There is still a problem. I have checked my
typing a dozen times and I think I typed it in OK but when I run it it gives
me a question box like a parameter query. Help!!!

 

[fredg]

I have a query that returns text. SOME text is just text but some text has a
number like X1027 - in front of the text. It might read like "X1027 -
Jonesboro High School". The text lengths vary from school to school and some
don't have the numbers in front. How can I remove the numbers, spaces, and -
in front of the ones that do and leave alone the ones that don't. Help!!!
Thanks so much!!!!!!

NewColumn:IIf(InStr([FieldName],"-")>0,Mid([FieldName],InStr([FieldName],"-")+2),[FieldName])

Change [FieldName] to whatever the actual field name is.

 

[John Spencer]

Assumption:
IF the field has a dash you want to trim off everything before the dash
(including the dash). Otherwise return the entire field

Try the following.

Trim(IIF([SchoolNameField] Like "*-*", Mid([SchoolNameField],
Instr(1,[SchoolNameField],"-")+1),[SchoolNameField]))

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Thanks so much, but now the field is returned with some of the last letters
trimed off, like Jonesboro High School comes back Jonesboro Hig. Help!!! I
want everything after the "X1047 - "Jonesboro High School if there is a
number. Thanks, so much

I have a query that returns text. SOME text is just text but some text has a
number like X1027 - in front of the text. It might read like "X1027 -
Jonesboro High School". The text lengths vary from school to school and some
don't have the numbers in front. How can I remove the numbers, spaces, and -
in front of the ones that do and leave alone the ones that don't. Help!!!
Thanks so much!!!!!!

NewColumn:IIf(InStr([FieldName],"-")>0,Mid([FieldName],InStr([FieldName],"-")+2),[FieldName])

Change [FieldName] to whatever the actual field name is.

 

[fredg]

Thanks so much, but now the field is returned with some of the last letters
trimed off, like Jonesboro High School comes back Jonesboro Hig. Help!!! I
want everything after the "X1047 - "Jonesboro High School if there is a
number. Thanks, so much

I have a query that returns text. SOME text is just text but some text has a
number like X1027 - in front of the text. It might read like "X1027 -
Jonesboro High School". The text lengths vary from school to school and some
don't have the numbers in front. How can I remove the numbers, spaces, and -
in front of the ones that do and leave alone the ones that don't. Help!!!
Thanks so much!!!!!!

NewColumn:IIf(InStr([FieldName],"-")>0,Mid([FieldName],InStr([FieldName],"-")+2),[FieldName])

Change [FieldName] to whatever the actual field name is.

The expression I gave you works as advertised for me, with even a
longer text value.

Original Data NewColumn
X34657 - Jonesboro High School Jonesboro High School
X1047 - Johannesburg High School Johannesburg High School

Perhaps you need to increase the width of the column to see the rest
of the text.

 

[Guest]

How could I be so stupid. I never even thought of that. Thanks to all of you.

Thanks so much, but now the field is returned with some of the last letters
trimed off, like Jonesboro High School comes back Jonesboro Hig. Help!!! I
want everything after the "X1047 - "Jonesboro High School if there is a
number. Thanks, so much

On Thu, 18 Oct 2007 07:59:05 -0700, Golfinray wrote:

I have a query that returns text. SOME text is just text but some text has a
number like X1027 - in front of the text. It might read like "X1027 -
Jonesboro High School". The text lengths vary from school to school and some
don't have the numbers in front. How can I remove the numbers, spaces, and -
in front of the ones that do and leave alone the ones that don't. Help!!!
Thanks so much!!!!!!

NewColumn:IIf(InStr([FieldName],"-")>0,Mid([FieldName],InStr([FieldName],"-")+2),[FieldName])

Change [FieldName] to whatever the actual field name is.

The expression I gave you works as advertised for me, with even a
longer text value.

Original Data NewColumn
X34657 - Jonesboro High School Jonesboro High School
X1047 - Johannesburg High School Johannesburg High School

Perhaps you need to increase the width of the column to see the rest
of the text.