Finding a space

[Louie Warren]

Have a database, with a table containing a Name field.
The field contains the last and first names of people;
both separated by a space. I want to find that space and
sort on the last name only. How can I do this? It
doesn't like substr. Left relys on knowing the end
position of the part you're looking for... as do MID and
right. What are my options... besides tearing it down and
redoing it with two separate fields? Thanx!

 

[Allen Browne]

Instr() will locate the space for you, so the Surname will be:
Mid([FullnameField], Instr([FullNameField, " ") + 1)

You do realize that it would be much better to have the surname in a
separate field, esp if you need to sort on it? Perhaps that's the reason you
want to locate the space - to separate into 2 separate fields with an Update
query.

 

[Gerald Stanley]

If the name column comprises "lastName firstName", try
something along the lines of
ORDER BY Left([name],InStr([name]," ")- 1)
If it is "firstName lastName" try
ORDER BY Right([name],Len([name])-InStr([name]," "))

Hope This Helps
Gerald Stanley MCSD

 

[Les]

Try INSTR function