Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Apr 2001
    Location
    California, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    InStr Function (A2K SR1)

    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.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: InStr Function (A2K SR1)

    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.
    Legare Coleman

  3. #3
    Lounger
    Join Date
    Apr 2001
    Location
    California, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: InStr Function (A2K SR1)

    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.

  4. #4
    Lounger
    Join Date
    Apr 2001
    Location
    California, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: InStr Function (A2K SR1)

    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.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: InStr Function (A2K SR1)

    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 ",".
    Legare Coleman

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: InStr Function (A2K SR1)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •