How to get name in right order

[#1]

Hi all,

I have a field called NameVZ which looks like this :    F.M.J. Hof van den

where F.M.J. are the initials, Hof is the surname and "van den" the preposition.

I'd like to create two fields out of this. One with the Initials which is easy ->  SubField(NameVZ, ' ',1) as Initials

The other field should have preposition + Surname in one:    "van den Hof"

But how do I do that as the propostion can be different for each name?

So to be clearer:

Field NameVZ   (examples)

F.M.J. Hof van den

T. Johnson

S. Haaren van

B. Beuker de

should become

Field FullnameVZ

van den Hof

Johnson

van Haaren

de Beuker

Preposition before the surname

[#2 · Accepted solution]

Thx Michael,

Your suggestion helped me on the right track.

This is what I've used now:

If(len(Trim(TextBetween(TextBetween(NaamVZ, ' ',''),' ','')))= 0, SubField(NaamVZ, ' ', 2), TextBetween(TextBetween(NaamVZ, ' ',''),' ','') & ' ' & SubField(NaamVZ, ' ', 2)) as AchternaamVZ

It's not 100% waterproof (e.g. da Silva de Jesus - >  Silva de Jesus Da) but it will do.

[Anonymous]

How about looping through a set of known name prepositions then. Something like this:

SET vName ='';

SET vEndName = textbetween(NameVZ, ' ', '');

FOR EACH vPrep IN ' van den',' van der',' van de',' de',' den'

if Lower(Right(vEndName,Len(vPrep))=vPrep THEN

   SET vName = vPrep & textbetween(vEndName, ' ', ''),

  EXIT FOR

ENDIF

NEXT

IF Len(vName)=0 THEN

   SET vName =vEndName

ENDIF

Note:

1. the spaces at the start of each of the listed vPrep options.

2. the 'van ..' options should go before those without 'van ..'. The EXIT FOR command should then prevent 'der' being used before 'van der'

Jonathan

[#4]

Thx for your effort Jonathan, but Michael already got me on the right track.

See above for the solution I used.