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 |
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 10:17 PM |
Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |