InStr Function Help 
Author Message
 InStr Function Help

I have a table with the following fields:

-Location
-UserID
-LastName
-FirstName

Only the Location field has data in it, and the data is always a first and
last name (John Smith, Bill Jones, etc.)  What I need to do is manipulate
that field and take parts of it out and put in into the three remaining
fields.  So, for John Smith, I would need to put "JSmith" in the User ID
field, "John" in the FirstName field, and "Smith" in the LastName field.

Here's all I have so far.  I need help putting the rest of the code together
to update the indiviaul fields in the recordset via code.

Dim SearchString, SearchChar, MyPos
SearchString = "John Smith"
SearchChar = " " ' Search for a space between the names
MyPos = InStr(SearchString, SearchChar) ' Finds where the space starts
[FirstName] = Mid(SearchString, 1, MyPos - 1) ' Returns first name
[LastName] = Mid(SearchString, MyPos + 1) ' Returns LastName
[UserID] = Left([FirstName], 1) + [LastName]

Any help is greatly appreciated.



Mon, 21 Apr 2003 01:13:51 GMT  
 InStr Function Help
I would use an update query to do this. WARNING: The SQL below is untested.  Try
it on a copy of your data.

UPDATE YourTablename SET
UserId = Left(Location,1) & Trim(Mid(Location,InStr(UserID," ")+1)),
LastName = Trim(Mid(Location,InStr(UserID," ")+1)),
FirstName = Trim(Left(Mid(Location,InStr(UserID," ")-1))

The above assumes that the field Location is exactly as you said.  If you have a
name such as "Mary Anne Jones-Smith" you would end up with:

 UserId: MAnne Jones-Smith
 LastName: Anne Jones-Smith
 FirstName: Mary        

Quote:

> I have a table with the following fields:

> -Location
> -UserID
> -LastName
> -FirstName

> Only the Location field has data in it, and the data is always a first and
> last name (John Smith, Bill Jones, etc.)  What I need to do is manipulate
> that field and take parts of it out and put in into the three remaining
> fields.  So, for John Smith, I would need to put "JSmith" in the User ID
> field, "John" in the FirstName field, and "Smith" in the LastName field.

> Here's all I have so far.  I need help putting the rest of the code together
> to update the indiviaul fields in the recordset via code.

> Dim SearchString, SearchChar, MyPos
> SearchString = "John Smith"
> SearchChar = " " ' Search for a space between the names
> MyPos = InStr(SearchString, SearchChar) ' Finds where the space starts
> [FirstName] = Mid(SearchString, 1, MyPos - 1) ' Returns first name
> [LastName] = Mid(SearchString, MyPos + 1) ' Returns LastName
> [UserID] = Left([FirstName], 1) + [LastName]

> Any help is greatly appreciated.



Mon, 21 Apr 2003 01:42:25 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. INSTR() FUNCTION: need help

2. HELP: Using InStr function to create a list box

3. instr & mid function in loop - Please help

4. Instr function

5. Bug in INSTR function

6. InStr Function does not work if start position is skipped

7. Instr Function Explanation Anyone ???

8. INSTR function flawed (QB45)

9. INSTR function NOT flawed (QB45)

10. Function InStr with VB 5 and Access 97

11. Proble with INSTR function in SQL statement

12. Using the Instr function

 

 
Powered by phpBB® Forum Software