Extracting data from string
in [Queries]
|
From: Mo on 13 Apr 2010 06:18 I'd like to extract data from a string variable. The data mostly looks like this: '456 585 8975 / MSKI 105'. An added complication is that the second part of the string does not have a consistent format. The format of the first part is always the same. I need to extract into two parts either side of the '/'. I can do this easily enough, for example, to extract first part: IIf (InStr ([NHS Number new/old],"/"), Left ([NHS Number new/old], InStr([NHS Number new/old],"/")-1)) And the second part: IIf (InStr ([NHS Number new/old],"/"), LTrim (Mid ([NHS Number new/old], 15))) However, some of the data will contain only the first part of the string ('456 585 8975'), or the second (' MSKI 105') without the '/'. I tried using the 'Format' function to do this, but with no success so far. Can anyone help? TIA
From: Stefan Hoffmann on 13 Apr 2010 06:37 hi Mo, On 13.04.2010 12:18, Mo wrote: > However, some of the data will contain only the first part of the string > ('456 585 8975'), or the second (' MSKI 105') without the '/'. Use IIf(): IIf(InStr([yourfield], "/") > 0, <bothParts>, <onlyOnePart>) mfG --> stefan <--
From: Mo on 13 Apr 2010 06:53 On 13/04/2010 11:37, Stefan Hoffmann wrote: > hi Mo, > > On 13.04.2010 12:18, Mo wrote: >> However, some of the data will contain only the first part of the string >> ('456 585 8975'), or the second (' MSKI 105') without the '/'. > Use IIf(): > > IIf(InStr([yourfield], "/") > 0, <bothParts>, <onlyOnePart>) > > > mfG > --> stefan <-- Thanks for your reply. I'm not sure what you mean by <bothparts> and <onlyonepart> Can you clarify please?
From: Stefan Hoffmann on 13 Apr 2010 07:41 hi Mo, On 13.04.2010 12:53, Mo wrote: > Thanks for your reply. I'm not sure what you mean by <bothparts> and > <onlyonepart> > > Can you clarify please? You insert your working snippets there as you need it. mfG --> stefan <--
From: John Spencer on 13 Apr 2010 09:10
FirstPart: IIF([NHS Number new/old] LIKE "### ### ####*",LEFT([NHS Number new/old],12), Null) SecondPart: More complex since you need to test for two conditions IIF([NHS Number new/old] Like "*/*" , Trim(Mid([NHS Number new/old],Instr(1,[NHS Number new/old],"/")+1)), IIF([NHS Number new/old] NOT LIKE "### ### ####", [NHS Number new/old],Null)) This does make the assumption that the NEW NHS number always has the format nnn nnn nnnn. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Stefan Hoffmann wrote: > hi Mo, > > On 13.04.2010 12:53, Mo wrote: >> Thanks for your reply. I'm not sure what you mean by <bothparts> and >> <onlyonepart> >> >> Can you clarify please? > You insert your working snippets there as you need it. > > > mfG > --> stefan <-- |