eliminating characters

[Guest]

I have a field that reads as follows "University of Texas Student", "Pacific
University Student". Is there a way I can get rid of the word "Student". It
is always at the end of the phrase. So I would be left with University of
Texas and Pacific University. Thank you for your help.....

Susie

 

[Roger Carlson]

I have a field that reads as follows "University of Texas Student", "Pacific
University Student". Is there a way I can get rid of the word "Student". It
is always at the end of the phrase. So I would be left with University of
Texas and Pacific University. Thank you for your help.....

Susie

 

[Guest]

Hi, Susie.


As there may be a variable number of spaces between the university name and
after "Student", a combination of RTrim, which removes trailing spaces, and
the Mid function, which returns a substring of an existing one, should do the
trick. You didn't say whether all records have this word, so I think you
should do this in two update queries. First remove any trailing spaces, then
remove the last seven letters if they are Student, STUDENT, student, or any
other combination:

First Update Query:
Update To: RTrim([YourField])

Second Update Query
IIf(UCase(Right([YourField],7))="STUDENT",RTrim(Mid([YourField],1,Len([YourField])-7)),[YourField])


Hope that helps.
Sprinks

 

[Roger Carlson]

If you are using Access 2000 or above, you can use the Replace function to
replace the string "Student" with the empty string (""). Something like
this: Replace([TheFieldName],"Student","").

In a query, you would use it like this:
UPDATE TheTable SET TheFieldName = Replace([TheFieldName],"Student","");

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L