To Use "RTrim" Or "Mid" for Sting 1

[WestView]

Hello All,

I have a single column field that contains both a name and an e-mail address:

Alice In Chains aichains@aol.com
Howdie Doodie hd@msn.com

I need a query that removes the e-mail address and leaves the name:

Alice In Chains
Howdie Doodie

Since the e-mail address is consistent - in that it has no spaces - I thought that would be the way to go using Mid(). But, I can’t get it right.

I would be grateful for any/all help or suggestions.

Thanks!!!

- Tom

 

[jebry]

Hi!

Try this:

Mid(YourField, InStrRev(Trim(YourField), " ") + 1)

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[Eupher]

Oops, Jeff, I think you got it backwards - I think Tom wants to strip the email and leave the name. I think this is what you meant:

Left([YourField], InStrRev(Trim([YourField), " ") - 1)

Ken S.

 

[r937]

wish i could test this but all i have is access97 and it doesn't have the InStrRev function

but hey, shouldn't the result of using InStrRev to find the first blank in the reverse direction actually be subtracted from the length of the field to come up with the second parameter for the LEFT function?



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts July 10 2005)

 

[Eupher]

rudy,

One would think so. It's a bit confusing, but InStrRev looks for the match string from the right, but returns the position where it is found from the left of the string. So in Westview's example "Howdie Doodie hd@msn.com" - InStrRev([MyField], " ") returns 14 - the first occurrence of the space character when looking from the right is at the 14th position from the left. So then we subtract 1 from that number to arrive at the number of characters to grab with the Left function.

I'm sure there's a workaround for Access 97 - let me fiddle with it and I'll see what I can come up with.

Ken S.

 

[PHV]

Have a look here:

http://www.tek-tips.com/faqs.cfm?fid=4342

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Eupher]

PHV, once again, right on the mark. Was just getting ready to post a bit of code, but the FAQ is much better.

Ken S.