Hi, Is it possible to write a qu 1

[twoody54]

Hi,

Is it possible to write a query that looks at a field (this case Name) and ignores the first word. The data is in this format "John Doe". I just want to look at Doe for my query.

Thanks
Tom

 

[jrbarnett]

It is possible, but needs a bit of VBA code to extract the last section of the name (must do that rather than everything after the first space, in case they are Jim Fred Bloggs for example).

I should point out that running VBA code from a query slows it down quite significantly.

John

 

[dodge20]

Check out this thread, it was for a name field that uses a comma, but you can adjust it accordingly

thread701-547412

Dodge20

 

[dodge20]

Also this might be easier, if all the data is in the same format.

SELECT Right([name],Len([name])-InStr([name]," &quot)
FROM customer;


Dodge20

 

[twoody54]

Wow guys thanks for the quick responses. You solution worked dodge thanks. I think there will be an occasional record like &quot;Billy Bob Thorton&quot; or something that it won't quite work for but that can be easily corrected as there wouldn't be many <20 out of about 2000.

Thanks a lot!
Tom