Find Blanks Spaces in Field using FIND Keyword

[JohnLee]

Hi,

Is it possible to use the Find Keyword in an expression in a query? I've done a search of this forum and couldn't find any specific for that.

I have imported data from an excel spreadsheet which has a field called FullName, which in my table in my database is called strFullname.

I have created an append query to append the data to a new table with only the information I want.

Now what I am trying to do is to obtain only the customers Title and Surname, I've worked out how to get the Title part, the tricky part is obtaining only the Surname part and putting it into a seperate field.

Here is an example of how the data looks:

Mr A Jones
Mrs B A Bolds
Mr Johnson

As I said above I've worked out how to get the salutation part [Title] but I'm having problems working out how to get the Surname aspect. This is my expression I've tried to use, which doesn't work, because Access says it doesn't recognise the Find Keyword.

Surname: IIf(Mid([strFullName]<>"",FIND(" ",[strFullName],0),20),"")

So what I'm trying to do here is if the field strFullName is not equal to nothing then FIND a space and return 20 characters to the right.

so from the above examples I want to only append the following

Jones
Bolds
Johnson

Clearly I'm doing something wrong, so any help would be greatly appreciated.

John

 

[namliam]

Find is excel, Instr or InstrRev is access

 

[JohnLee]

Thanks Namliam,

I use that and let you know how I get on.

Cheers

John

 

[JohnLee]

Hi Namliam,

Unfortunately the Instr or the instrRev didn't work, I get an #error in the query view. Here's the expression:

Surname: Trim(IIf([strFullName]<>"",Mid([strFullName],InstrRev(" ",[strFullName],0),20),""))

Perhaps you can see where I'm going wrong.

Your assistance would be most appreciated.

John

 

[namliam]

Read the help on Instr and InstrRev

 

[JohnLee]

Hi again,

unfortunately, when I do a search for Instr and instrRev in access it responds with the message "I don't know what you mean, Please rephrase the question", which I did and the same response is repeated.

So can you give a little more help, is the expression I produced drastically wrong and if so where am I going wrong, that would be more helpful than keep referring me to read stuff that access help doesn't provide.

Thanks in advance

John

 

[namliam]

For your enjoyment:
http://msdn.microsoft.com/en-us/library/hsxyczeb(VS.85).aspx
http://msdn.microsoft.com/en-us/library/8460tsh1(VS.80).aspx

 

[JohnLee]

Hi Namliam,

I have looked up those reference you supplied and I have a better understanding, my expression now looks like this:

Surname: Trim(Right([strFullName],InStrRev([strFullName]," ")-10))

I put in the minimum number of characters to return, but in reality I want it to return all the characters it finds to the right of the space, not just 10, the problem is when it finds the space the above expression doesn't give me all the characters to the right of the space found as follows:

"Banks" it only returns "ks"
"Akehurst" it only returns "hurst"
"Bernard" it only returns "nard"

and so on

I guess I'm moving in the right direction above, but can't work out how to get my expression to return all the characters to the right of the space found.

Can you help me here

John

 

[Brianwarnock]

Surname: Trim(Right([strFullName],InStrRev([strFullName]," ")-10))

Having trimmed all the blanks you have asked for the right most characters for a length of the position of the rightmost blank -10, what you actually require is a length of the length of the field - the position of the blank

eg if the field is 24 characters and the right most blank is in position 20 then the surname is 4 characters.


An alternative is to use the Mid instruction where the starting position is the right most blank+1 and let the length default , it will give the rest of the field

Brian

 

[rainman89]

An alternative is to use the Mid instruction where the starting position is the right most blank+1 and let the length default , it will give the rest of the field

Never used that before! I didn't know that's how it worked! I've always used

Len(fullname)-instr(blah)

Thanks for that.

 

[BradWare]

I played around with your data and this worked for me:

Surname: Right([strFullName],Len([strFullName])-InStrRev([strFullName]," ",-1))

I did make the assumption that there are no spaces AFTER the surname in your raw data.

 

[Brianwarnock]

I've always used

Len(fullname)-instr(blah)

Ditto until I saw it used on this forum and so I tried it and thought "expression deleted, all that effort I used to go to."

Brian

 

[Brianwarnock]

I played around with your data and this worked for me:

Surname: Right([strFullName],Len([strFullName])-InStrRev([strFullName]," ",-1))

I did make the assumption that there are no spaces AFTER the surname in your raw data.

Yep that's what I told him to do, the -1 is the default on the InstrRev

I usually use the Mid approach

The Trim removed the spaces.

Brian

 

[namliam]

Hi Namliam,

I have looked up those reference you supplied and I have a better understanding, my expression now looks like this:

InstrRev counts the characters from the back (rev = Reverse), finding the last space.
Thus, if fullname is "The national version of the ABNAMRO BANK", your instrRev will return 36
As in the 36th character FROM THE LEFT of your string, obviously doing Right (string, 36) will not return bank...
instead you want Right(string,4) to return bank.
Using Len(string) we find that the total string is 40.
40 - 36 == 4, the magic 4 we want.

Another option as others have noted is using the Mid to take all characters from the instrrev function.

 

[Brianwarnock]

I can never understand why some find it necessary to explain what has already been explained and illustrated.

Brian