if I know the last name can I get the first name from the full name?

[Kelvin Beaton]

I'm inporting data that has the person's full name in one field. i.e.
"GLORIA DE LOS SANTOS".
But they have given me a last name in a separate field, and it's listed as
"LOS SANTOS".

Since I know the last name, can I extract everything excluding the last name
from the full name?
I don't mind if they included the middle initial and that was on the end of
the first name. I could live with that.

Thanks

Kelvin

 

[DCPan]

Well, one way to do it is just use the length function and pull from the full
name field the field length of the full name field minus length of last name
-1 1...got the minus one in there to remove the extra space.

 

[mray29]

You can use this query:
SELECT Table1.WholeName, Left([WholeName],Len([WholeName])-Len([LastName]))
AS Firstname
FROM Table1;

 

[Kelvin Beaton]

Thanks a million for the example!

Thanks

Kelvin

You can use this query:
SELECT Table1.WholeName,
Left([WholeName],Len([WholeName])-Len([LastName]))
AS Firstname
FROM Table1;

I'm inporting data that has the person's full name in one field. i.e.
"GLORIA DE LOS SANTOS".
But they have given me a last name in a separate field, and it's listed
as
"LOS SANTOS".

Since I know the last name, can I extract everything excluding the last
name
from the full name?
I don't mind if they included the middle initial and that was on the end
of
the first name. I could live with that.

Thanks

Kelvin

 

[Kelvin Beaton]

This is what I ended up with.... and it works great!
Firstname:
StrConv(RTrim(Left([full_name],Len([full_name])-Len([last_name]))),3)

This gives me the first name and middle initial if there is one, trims any
blank spaces off the right end of the string and puts the text in proper
case...

Thanks for your help!!!

Kelvin

You can use this query:
SELECT Table1.WholeName,
Left([WholeName],Len([WholeName])-Len([LastName]))
AS Firstname
FROM Table1;

I'm inporting data that has the person's full name in one field. i.e.
"GLORIA DE LOS SANTOS".
But they have given me a last name in a separate field, and it's listed
as
"LOS SANTOS".

Since I know the last name, can I extract everything excluding the last
name
from the full name?
I don't mind if they included the middle initial and that was on the end
of
the first name. I could live with that.

Thanks

Kelvin

 

[John Nurick]

Hi Kelvin,

This is reasonably easy. Rather than importing the data, link to it.
I'll assume that fields in the linked table are called FullName and
LastName.

Then use an append query to move the data into your 'real' table,
truncating the full name to get the short name as you go.

This is just a matter of getting the number of characters in the last
name and omitting that number of characters + 1 from the full name.

So the append query needs a calculated field for the FirstName field
in your 'real' table, like this:

FirstName: Left([Fullname], Len([FullName]) - Len([LastName]) - 1)

 

[Brent]

Hi Kelvin,

Use replace("GLORIA DE LOS SANTOS","LOS SANTOS","")
The function returns "GLORIA DE ". If you don't need the space at the end,
just use RTRIM function to remove it.

Brent

 

[John Spencer]

I would be cautious using the Replace function. In 99% plus of the case it
would work well, but if the last name should ever be included in the first
name you would get strange results.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Kelvin Beaton]

Thanks for the reply, this works great!

As far as linking to the spreadsheet that seems fine, but the file name is
always different.
I guess I could allways call it "Import" or something and that could work.
I do about the same thing importing the data. Import it, rename it to the
table I use for the process.
The Excel file is password protected, so I have to deal with that in the
process anyway.

Thanks for the input and ideas....

Kelvin

Hi Kelvin,

This is reasonably easy. Rather than importing the data, link to it.
I'll assume that fields in the linked table are called FullName and
LastName.

Then use an append query to move the data into your 'real' table,
truncating the full name to get the short name as you go.

This is just a matter of getting the number of characters in the last
name and omitting that number of characters + 1 from the full name.

So the append query needs a calculated field for the FirstName field
in your 'real' table, like this:

FirstName: Left([Fullname], Len([FullName]) - Len([LastName]) - 1)

I'm inporting data that has the person's full name in one field. i.e.
"GLORIA DE LOS SANTOS".
But they have given me a last name in a separate field, and it's listed as
"LOS SANTOS".

Since I know the last name, can I extract everything excluding the last
name
from the full name?
I don't mind if they included the middle initial and that was on the end
of
the first name. I could live with that.

Thanks

Kelvin

 

[Kelvin Beaton]

Looking back they haven't been consistant in the tab names either...
So unless there is a way to have it prompt the user for the file name and
tab name, I think I'll have to do a little tweeking...

Thanks

Kelvin

Hi Kelvin,

This is reasonably easy. Rather than importing the data, link to it.
I'll assume that fields in the linked table are called FullName and
LastName.

Then use an append query to move the data into your 'real' table,
truncating the full name to get the short name as you go.

This is just a matter of getting the number of characters in the last
name and omitting that number of characters + 1 from the full name.

So the append query needs a calculated field for the FirstName field
in your 'real' table, like this:

FirstName: Left([Fullname], Len([FullName]) - Len([LastName]) - 1)

I'm inporting data that has the person's full name in one field. i.e.
"GLORIA DE LOS SANTOS".
But they have given me a last name in a separate field, and it's listed as
"LOS SANTOS".

Since I know the last name, can I extract everything excluding the last
name
from the full name?
I don't mind if they included the middle initial and that was on the end
of
the first name. I could live with that.

Thanks

Kelvin

 

[fredg]

So using your example, the person's first name is "Gloria De"?
Somehow I don't think so. I would think just "Gloria" is more accurate, as
the last name should include the "DE".
In any event, another expression to remove the know last name from a
fullname, in a query, might be:
FirstName:Replace([FullName],[LastNameField],"")
You can add the StrConv() and Trim() functions if needed.

This is what I ended up with.... and it works great!
Firstname:
StrConv(RTrim(Left([full_name],Len([full_name])-Len([last_name]))),3)

This gives me the first name and middle initial if there is one, trims any
blank spaces off the right end of the string and puts the text in proper
case...

Thanks for your help!!!

Kelvin

You can use this query:
SELECT Table1.WholeName,
Left([WholeName],Len([WholeName])-Len([LastName]))
AS Firstname
FROM Table1;

I'm inporting data that has the person's full name in one field. i.e.
"GLORIA DE LOS SANTOS".
But they have given me a last name in a separate field, and it's listed
as
"LOS SANTOS".

Since I know the last name, can I extract everything excluding the last
name
from the full name?
I don't mind if they included the middle initial and that was on the end
of
the first name. I could live with that.

Thanks

Kelvin

 

[Kelvin Beaton]

Thanks I'll give that a try also

Kelvin

So using your example, the person's first name is "Gloria De"?
Somehow I don't think so. I would think just "Gloria" is more accurate, as
the last name should include the "DE".
In any event, another expression to remove the know last name from a
fullname, in a query, might be:
FirstName:Replace([FullName],[LastNameField],"")
You can add the StrConv() and Trim() functions if needed.

This is what I ended up with.... and it works great!
Firstname:
StrConv(RTrim(Left([full_name],Len([full_name])-Len([last_name]))),3)

This gives me the first name and middle initial if there is one, trims
any
blank spaces off the right end of the string and puts the text in proper
case...

Thanks for your help!!!

Kelvin

You can use this query:
SELECT Table1.WholeName,
Left([WholeName],Len([WholeName])-Len([LastName]))
AS Firstname
FROM Table1;




:

I'm inporting data that has the person's full name in one field. i.e.
"GLORIA DE LOS SANTOS".
But they have given me a last name in a separate field, and it's
listed
as
"LOS SANTOS".

Since I know the last name, can I extract everything excluding the
last
name
from the full name?
I don't mind if they included the middle initial and that was on the end
of
the first name. I could live with that.

Thanks

Kelvin