Query for last name, please help

  • Thread starter Thread starter broogle
  • Start date Start date
B

broogle

My data :

Field name : Progress

15-Dec-03 18:18 Angela Bennet: Start packing all the old stuff.....
15-Jan--04 18:18 Shila Williamson: Start packing all the new
stuff.....

Just want to create an update query which only take the surename only,

Progress_Surename:
Bennet
Williamson

Any idea???

Thanks a million
 
I can't be sure, but your example makes it look like you are storing a date,
a time, a firstname, a lastname, a text-based description ... and all in a
single field ([Progress]).

How is Access to "know" where in this field (string of characters) the
"surname" is located?

If you are keeping multiple facts in one field, first consider redesigning
your table structure to achieve "one fact, one field".

If you are absolutely, positively convinced that the portion of the string
you want occurs immediately after, say, the third space, you might also
consider using the Split() function in a query to extract that portion of
the string.
 
Is all that data in one field?
Is it consistently entered? If not then a solution is more problematic.

What version of Access are you using?

If you are using a later version of Access (2000 or later) you can use a VBA
function and the Split function to return the column

UNTESTED AIR CODE

Public Function GetLastName(FieldIN) as Variant
Dim arSplit as Variant

IF Len(Trim(FieldIn & "")) = 0 Then
GetLastName = FieldIN
Else
arSplit = Split(FieldIn," ",-1,vbTextCompare)
If Ubound(ArSplit) >2 Then
'Get the value and strip off the colon at the end
GetLastName = Left(arSplit(3),Len(arSplit(3))-1)
Else
GetLastName = Null
End IF
End IF
End Function

Copy that function to a module and save the module (with a name other than
GetLastName). Now use the module in your query.
Field: Progress_SurName: GetLastName(Progress)
 
Back
Top