Searching based upon second word in a field

[orbic1]

Hi,

I have a table with the following fileds:

ID, Name, DOB, Ref

Name is in the Form: Forename (Initial/Middle Name) Surname

I have a form that lists all of this data, but I need to sort by Surname. How would I go about it?

Thanks

 

[WayneRyan]

orbic1,

1) Name is a reserved word - will to problems for you
2) You should break your current namefield up into its
component sub-fields. Then this would be an easy task.

New column in your query:

SurName: Mid([Name], InStrRev([Name], " ") + 1)

Wayne

 

[orbic1]

Thanks, Wayne

1) I do have a prefix to "name" - forgot! Still, thanks for the heads-up
2) Get an error: Too complex to be evaluated

Any ideas?

Cheers

 

[WayneRyan]

Orbic,

SurName: Mid([Name], InStr(1, [Name], " ") + 1)

If that doesn't work, it's probably because of the field called "Name".

Wayne

 

[orbic1]

Thanks - that works beautifully!
Is there any way of making it is by default +2, and if there is no middle name, +1 ?

Thanks

 

[WayneRyan]

Orbic,

If the presence (or absence) of a slash was definitive, you could use an
IIf statement. If you like, I'll post a sample later.

Wayne

 

[orbic1]

Thanks - will give it a shot!

 

[WayneRyan]

orbic,

You can use the InStrRev function from earlier in this thread.
It will look for a space beginning at the end of the whole name
and working backwards. It won't matter if they have a middle
name/initial or not.

Wayne