query to seperate a last name from a name field containing LastNam

[Guest]

I retrieved a database created in FoxPro. I now have the tables in Access
2002. I want to create a table which will give me the contacts by [LastName]
[FirstName] in seperate fields. the database currently has one filed with
LastName, FirstName Mi SpouseName Mi. is there a way to query the field and
retrieve the LastName only?

 

[John Webb via AccessMonster.com]

Gary,

Considering that Lastname is the first part of the field, this should be
fairly straight forward. Enter the formula:

Left$([YOUR FIELD],InStr(1,[YOUR FIELD]," ")-1)

However, you might run into difficulties if you come across a double
barrelled surname without a "-" in it. Unfortunately, unless the field is
of a fixed width I don't know any way of doing this except manually
checking.

Just hope that any double barrelled surnames are held with a "-".

Cheers

John Webb

 

[Guest]

Good Morning John, Well, now I am wondering does this go in the criteria box
for my query? I placed it there and get nothing. sorry I am just learning
and boy I have lots to learn, I am a student as well and I want to be able to
work with access properly. sometimes I just to get it correct! I appreciate
your help
Gary

 

[Guest]

Hi John, got it! but I don't need the comma so I will change to eliminate
that! I see now that when I place the formula in the next column and change
the "Expr: entry to the name of the column I want I get the result I am
looking for. very powerful, thank you so much! I have lots more to do with
this database and I am looking forward to becoming competent! again thank you
gary

 

[John Webb via AccessMonster.com]

Not a problem Gary,

I wish you luck on your quest for knowledge!