InStr Function (A2K SR1)

Topic

New Reply

Could someone explain the use of InStr or point me to a resource that explains it so I can understand the following query (not developed by me):

IIf(InStr([Owner],”,”),Trim(Right(Trim([Owner]),Len(Trim([Owner]))-InStr(1,[Owner],”,”))) & ” ” & Trim(Left([Owner],InStr(1,[Owner],”,”)-1)),[Owner])

The expert who developed this for me is lost in the mists of time. I understand the syntax and function up to the point where you subtract based on InStr(1,[Owner],”,”))) etc. I don’t understand why the subtraction is used, why the 1 is in the parantheses, etc.

Thanks for your help.

Reply | Quote

Replies

It looks like the table contains a field “Owner” which contains a Name in the form LastName,FirstName. So, John Smith would be in the field as Smith, John. InStr returns the position of the search string in the source string. Therefore, if Owner contains Smith, John, then InStr([Owner],”,”) will return 6 since the comma is in the 6th character position. InStr([Owner],”,”)-1 would be 5 which is the length of the name in front of the comma.

This statement will return John Smith if the field contains Smith, John.

Reply | Quote

Thank You! I had no idea it returned the position. I’ve looked all through the online help, the Que Access 2000 book, and the Access 2000 manuals without finding a description of the function. Maybe I overlooked it.

Thanks again.

Reply | Quote

You should be able to find it in the on-line help (I did). However, bring up the visual basic window first (Alt-F11). The help available in Access itself is different from the help available in visual basic.

Reply | Quote

A follow up question. If the data is as follows:

Smith, John
Jones Industries

what would InStr(1,[Owner]) return? Why is the number 1 in front of the field name? I don’t see how this works since InStr searches for something within a string, and 1 isn’t a string as used in the argument.

Thanks for your help.

Reply | Quote

For Smith, John, InStr would return 6. For Jones Industries it would return 0 (indicating that the search string is not in the source string).

The InStr function has an optional first parameter (yes, it is very unusual for the first parameter to be optional). If the first parameter is specified, it must be a number, not a string, and it indicates which character in the source string to start the search. The default is 1, so it is not necessary to specify it in this case. In your example, you left out the last parameter which should have been “,”.

Reply | Quote