A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Expression to middle name/initial from "Last, First Middle"



 
 
Thread Tools Display Modes
  #1  
Old June 24th, 2005, 06:14 PM
Tim
external usenet poster
 
Posts: n/a
Default Expression to middle name/initial from "Last, First Middle"

I am trying to write an expression for an update query, that will capture the
middle name/initial from a [Name] field that is formatted as "Last, First
Middle/MI". The problem is that there is not always a middle name/initial. So
some data is formatted as "Last, First". I need it to capture the middle
name/initial when it occurs and nothing when it doesn't.

I need it to do this:
Original Entry in [Names]: "Doe, John P."
Returned by Expression: P.
Expression: Expr: Right(Trim([Names]),Len(Trim([Names]))-InStr _
(InStr(1, [Names]," ")+1,[Names]," "))

But like this (leaving the field blank when there is no middle name):
Original Entry in [Names]: "John Doe" or "John P. Doe"
Returned by Expression: P. or blank
Expression: Expr: Trim(Mid([names], InStr(1, [names], " ") + 1,
IIf(InStr(InStr(1, [names], " ") + 1, [names], " ") = 0, 0, InStr(InStr(1,
[names], " ") + 1, [names], " ") - InStr(1, [names], " "))))


Thanks
Tim
  #2  
Old June 24th, 2005, 06:28 PM
Jeff Boyce
external usenet poster
 
Posts: n/a
Default

Tim

Are you ABSOLUTELY certain that every name with a Middle Initial has a
period (.) after it?

If so, any namefield that had Right([NameField],1) = "." should have an
initial and a period in the last two places. So your test would be
something like:
if there is no period, blank,
otherwise, get the last two and use the first one

Perhaps you could approach this backwards?

Good luck

Jeff Boyce
Access MVP

"Tim" wrote in message
...
I am trying to write an expression for an update query, that will capture
the
middle name/initial from a [Name] field that is formatted as "Last, First
Middle/MI". The problem is that there is not always a middle name/initial.
So
some data is formatted as "Last, First". I need it to capture the middle
name/initial when it occurs and nothing when it doesn't.

I need it to do this:
Original Entry in [Names]: "Doe, John P."
Returned by Expression: P.
Expression: Expr: Right(Trim([Names]),Len(Trim([Names]))-InStr _
(InStr(1, [Names]," ")+1,[Names]," "))

But like this (leaving the field blank when there is no middle name):
Original Entry in [Names]: "John Doe" or "John P. Doe"
Returned by Expression: P. or blank
Expression: Expr: Trim(Mid([names], InStr(1, [names], " ") + 1,
IIf(InStr(InStr(1, [names], " ") + 1, [names], " ") = 0, 0, InStr(InStr(1,
[names], " ") + 1, [names], " ") - InStr(1, [names], " "))))


Thanks
Tim



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem using Query Criteria with an Expression Carol Running & Setting Up Queries 6 June 21st, 2005 09:03 PM
Unable to open query with Complex Expression Lex Running & Setting Up Queries 1 April 2nd, 2005 04:44 PM
Can Excel represent formula in textural format with values substi. BoneR Worksheet Functions 7 March 31st, 2005 03:11 PM
expression builder erased my expression Stefano General Discussion 1 December 6th, 2004 11:17 PM
Here's a shocker Mike Labosh General Discussion 2 October 26th, 2004 05:04 PM


All times are GMT +1. The time now is 09:30 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.