Retrieving first word of field????

[fatdog302]

I have and access database (linked tables from a SQL DB),
For some reason a few thousand records have the Full name of a person in the last_name field and the first_name field is null.

Last_Name = 'John Citizen'
First_Name is null

i need

Last_Name = 'Citizen'
First_Name = 'John'

So what i was wondering was whether there is any way to query/update that will take the first word (which in all cases is the first name)of the last_name field and put it in the first_name field. I hope this makes sense.

 

[scriverb]

Paste this code into the SQL property of a new query. You will have to change the tblYourTable throughout the code to the name of your table and this should do it. I checked first for Nulls in the First_Name field and then parsed the Last_name data to both fields. Make sure you do a test on a copy of a few of the records before you run the whole file. The only thing that might not parse correctly are first names like Jo Ellen, etc. where there is a space in the first name legitimately. I started at the left looking for the first space and used that to split the data apart.

UPDATE tblYourTable SET tblYourTable.First_Name = Mid$([tblYourTable]![Last_Name],1,InStr(1,[tblYourTable]![Last_Name]," &quot-1), tblYourTable.Last_Name = Mid$([tblYourTable]![Last_Name],InStr(1,[tblYourTable]![Last_Name]," &quot+1)
WHERE (((tblYourTable.First_Name) Is Null));

Good luck

Bob Scriver

 

[THoey]

To solve the problem about the possibility of Billy Bob Jo Ray Smith, instead of going from the LEFT of the field, go from the RIGHT. Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[scriverb]

You certainly can go from the right but that requires a seperate Function to be written to search for the space in the field from the right. ACCESS only provides an Instr function. This would be an InstrRight function which I can provide if that is needed.

Just let me know.

Bob Scriver

 

[fatdog302]

Thank you so much, that was my first post, my faith in the internet has been restored!!!

 

[THoey]

You might want to check it out, but I thought I saw somewhere that if you give a negative number for the starting position of an INSTR() function, it starts from the right. Don't have time to test it now, but it is worth a try... Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[scriverb]

THoey: No, the Instr Function does not accept a negative number as the starting point to initiate it to start from the right. Specialty Functions such as an InstrRight function which I have written meets this particular need. It returns the position of a particular string variable withing a string but searches from a position withing the string back to the left, so it is called InstrRight:

Public Function InStrRight(vSearchStr As String, vTargetStr As String, vStart As Long) As Long
Dim i As Integer
For i = vStart To 1 Step -1
If InStr(i, vSearchStr, vTargetStr, 1) = i Then
InStrRight = i 'Position of vTargetStr
Exit For
End If
Next i
End Function

You can copy this function an place it in a Global database module. And , then make a call to it in the following fashion using the above example:

vLastName = Trim( Mid$( [tblYourTable]![Last_Name], InstrRight( [tblYourTable]![Last_Name]," ", Len([tblYourTable]![Last_Name])+1 ) )

The three parameters being passed to the InstrRight function are: vSearchString = string being searched(Sally Jones) vTargetStr = String you are looking for(space-" &quot and vStart = the right character starting point(Lenth of string - 11). The function then starts at vStart(11) and searches back to the left in vSearchString(Sally Jones) looking for the beginning position of vTargetStr)" &quot. It returns the value 6.

I hope that this functions helps anyone that needs it.

Bob Scriver