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
|
|||
|
|||
Instr help
Hello,
I've spent quite a bit of time in Excel using the SEARCH function to parse out names from a text field thinking I'd be able to use this same expression once I brought the data into Access. As I find out I guess I need to be using the InStr function in Access but I'm still having a little trouble. Here's an example of the text field : 00022956CTE/Smith,Harry And here's an example of the SEARCH function I used in Excel which produced "Harry": =+RIGHT(C2,LEN(C2)-SEARCH(",",C2,1)) When I try to run an Update query to a blank field with this in Access replacing "SEARCH" with "InStr" I get a "Type conversion error" on all of the records. Can anyone assist? I have a feeling I'm missing something fairly obvious here. Thanks! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ccess/201005/1 |
#2
|
|||
|
|||
Instr help
Note that InStr, the string you searching for comes 2nd, not first. =Right([FieldName],Len([FieldName])-InStr([FieldName],",")) |
#3
|
|||
|
|||
Instr help
On Wed, 19 May 2010 19:46:33 GMT, "fhurman via AccessMonster.com" u43637@uwe
wrote: Hello, I've spent quite a bit of time in Excel using the SEARCH function to parse out names from a text field thinking I'd be able to use this same expression once I brought the data into Access. As I find out I guess I need to be using the InStr function in Access but I'm still having a little trouble. Here's an example of the text field : 00022956CTE/Smith,Harry And here's an example of the SEARCH function I used in Excel which produced "Harry": =+RIGHT(C2,LEN(C2)-SEARCH(",",C2,1)) When I try to run an Update query to a blank field with this in Access replacing "SEARCH" with "InStr" I get a "Type conversion error" on all of the records. Can anyone assist? I have a feeling I'm missing something fairly obvious here. Thanks! We're not there, and we can't see your screen. You clearly didn't use that exact expression with CELL references - Access is not a flawed implementation of Excel! To extract the text after the comma you would use an expression: Mid([fieldname], InStr([fieldname], ",") + 1) -- John W. Vinson [MVP] |
#4
|
|||
|
|||
Instr help
Easiest way if your string contains only one comma.
Mid(SomeString,Instr(1,SomeString,",")+1) John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County fhurman via AccessMonster.com wrote: Hello, I've spent quite a bit of time in Excel using the SEARCH function to parse out names from a text field thinking I'd be able to use this same expression once I brought the data into Access. As I find out I guess I need to be using the InStr function in Access but I'm still having a little trouble. Here's an example of the text field : 00022956CTE/Smith,Harry And here's an example of the SEARCH function I used in Excel which produced "Harry": =+RIGHT(C2,LEN(C2)-SEARCH(",",C2,1)) When I try to run an Update query to a blank field with this in Access replacing "SEARCH" with "InStr" I get a "Type conversion error" on all of the records. Can anyone assist? I have a feeling I'm missing something fairly obvious here. Thanks! |
#5
|
|||
|
|||
Instr help
Thanks this is helpful. As I get further with other nomencaltures does the
logic for InStr generally work the same as the SEARCH function in excel where you can use many nested expressions to form your basis? John Spencer wrote: Easiest way if your string contains only one comma. Mid(SomeString,Instr(1,SomeString,",")+1) John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Hello, [quoted text clipped - 13 lines] Thanks! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ccess/201005/1 |
#6
|
|||
|
|||
Instr help
Sorry, not enough knowledge on my part to understand the question.
John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County fhurman via AccessMonster.com wrote: Thanks this is helpful. As I get further with other nomencaltures does the logic for InStr generally work the same as the SEARCH function in excel where you can use many nested expressions to form your basis? John Spencer wrote: Easiest way if your string contains only one comma. Mid(SomeString,Instr(1,SomeString,",")+1) John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Hello, [quoted text clipped - 13 lines] Thanks! |
#7
|
|||
|
|||
Instr help
If I want to parse the last name "Smith" out of the expression "Airtick /
Smith Harry M" how do I use a nested Instr to get my proper length of my expression? The starting point I have down but in this case the length would need to be the number generated by the 3rd space minus the number generated by the 2nd space. John Spencer wrote: Sorry, not enough knowledge on my part to understand the question. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Thanks this is helpful. As I get further with other nomencaltures does the logic for InStr generally work the same as the SEARCH function in excel where [quoted text clipped - 12 lines] [quoted text clipped - 13 lines] Thanks! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ccess/201005/1 |
#8
|
|||
|
|||
Instr help
Dim lngNextBlank As Long
Dim lngStart As Long Dim strInput As String strInput = "Airtick / Smith Harry M" lngStart = InStr(strInput, "Smith") lngNextBlank = Instr(lngStart, strInput + " ", " ") To get Smith, you can then use Mid(strStart, lngStart, lngNextBlank - lngStart) -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "fhurman via AccessMonster.com" u43637@uwe wrote in message news:a856ab1d029d0@uwe... If I want to parse the last name "Smith" out of the expression "Airtick / Smith Harry M" how do I use a nested Instr to get my proper length of my expression? The starting point I have down but in this case the length would need to be the number generated by the 3rd space minus the number generated by the 2nd space. John Spencer wrote: Sorry, not enough knowledge on my part to understand the question. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Thanks this is helpful. As I get further with other nomencaltures does the logic for InStr generally work the same as the SEARCH function in excel where [quoted text clipped - 12 lines] [quoted text clipped - 13 lines] Thanks! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ccess/201005/1 |
Thread Tools | |
Display Modes | |
|
|