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 » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Instr help



 
 
Thread Tools Display Modes
  #1  
Old May 19th, 2010, 08:46 PM posted to microsoft.public.access
fhurman via AccessMonster.com
external usenet poster
 
Posts: 5
Default 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  
Old May 19th, 2010, 09:05 PM posted to microsoft.public.access
ghetto_banjo
external usenet poster
 
Posts: 325
Default Instr help


Note that InStr, the string you searching for comes 2nd, not first.



=Right([FieldName],Len([FieldName])-InStr([FieldName],","))



  #3  
Old May 19th, 2010, 09:23 PM posted to microsoft.public.access
John W. Vinson
external usenet poster
 
Posts: 18,261
Default 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  
Old May 19th, 2010, 09:28 PM posted to microsoft.public.access
John Spencer
external usenet poster
 
Posts: 7,815
Default 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  
Old May 19th, 2010, 09:40 PM posted to microsoft.public.access
fhurman via AccessMonster.com
external usenet poster
 
Posts: 5
Default 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  
Old May 19th, 2010, 10:00 PM posted to microsoft.public.access
John Spencer
external usenet poster
 
Posts: 7,815
Default 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  
Old May 21st, 2010, 03:18 PM posted to microsoft.public.access
fhurman via AccessMonster.com
external usenet poster
 
Posts: 5
Default 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  
Old May 21st, 2010, 04:52 PM posted to microsoft.public.access
Douglas J. Steele[_3_]
external usenet poster
 
Posts: 3,143
Default 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

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


All times are GMT +1. The time now is 09:58 AM.


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