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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 |