I have a field called full name and a field called password. I would like to be able to automatically generate a password from this field e.g. my name is John Smith so I want my password to be the first 3 letters of my first name and and the first 3 of my second name, But I want to do this without using 2 fields for my first & second name. I can use 'left words' for the first 3 letters of my first name but how can I get the 3 letters of my second name??

PS there may sometimes be only 1 name in the name field.

I guess it goes without saying that passwords derived from peoples names are not exactly secure?! Notwithstanding that, if its what you need to do - for whatever reason, it is certainly possible.

You could try:

Lower(Left(full name, 3) & Left(RightWords(full name, 1), 3))

This will give you johsmi for "John Smith" and johjoh for "John".

If you do not want it to repeat the first three characters of the first name where there is only one name in the field, you could use:

Lower(Left(full name, 3) & Case(WordCount(full name) > 1, Left(RightWords(full name, 1), 3)))

This will give you johsmi for "John Smith" and joh for "John".