Parsing Query

[Diesel]

I am importing a text file which includes a field titled [lastname]. In some
of these cells the lastname has a suffix attached to it (e.g. Jr., IV, Sr.,
etc). I want an update query to be able to go through and parse the suffix
out of the last name. I have attempted several queries and have been
unsuccessful. I am trying to make a query that updates the [lastname] field
to just the lastname and no suffix.

Previous attempts:
Left$([lastname],InStr(1,[lastname]," ")-1)

Any suggestions

 

[Tom Ellison]

Dear Diesel:

Is there always a space before the suffix you want to remove? Is
there ever a space within a last name without a suffix? What went
wrong using the code in your "Previous attempt"?

What you will need could be a custom solution. Your data, and the
challenges it presents, are likely to be unlike those of another
database. Often, this takes trial and error to determine the best
solution.

I was thinking you might want a table of possible suffixes for which
to search. That's just one approach, but it's a relatively powerful
one, while being a bit of work to implement. The specific compromise
between difficulty and accuracy that will be best for you is not
something we can prescribe from a distance.

If there are significantly large problems with your previous solution,
then knowing just what those problems are is key to being able to even
begin to suggest what the next step may be.

Get us some specific description of the challenges that remain and
likely someone can try to give you some suggestions.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Diesel]

The result of that code produced the suffix staying in the lastname field.
As the lastname value I wanted to stay was erased. I tried reversing the
code but inserting Right$ instead of Left$ and it returned blank values
except for those values that included two words in the lastname field (eg.
Smith Jr.) showed up as "lastname]"

Dear Diesel:

Is there always a space before the suffix you want to remove? Is
there ever a space within a last name without a suffix? What went
wrong using the code in your "Previous attempt"?

What you will need could be a custom solution. Your data, and the
challenges it presents, are likely to be unlike those of another
database. Often, this takes trial and error to determine the best
solution.

I was thinking you might want a table of possible suffixes for which
to search. That's just one approach, but it's a relatively powerful
one, while being a bit of work to implement. The specific compromise
between difficulty and accuracy that will be best for you is not
something we can prescribe from a distance.

If there are significantly large problems with your previous solution,
then knowing just what those problems are is key to being able to even
begin to suggest what the next step may be.

Get us some specific description of the challenges that remain and
likely someone can try to give you some suggestions.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

I am importing a text file which includes a field titled [lastname]. In some
of these cells the lastname has a suffix attached to it (e.g. Jr., IV, Sr.,
etc). I want an update query to be able to go through and parse the suffix
out of the last name. I have attempted several queries and have been
unsuccessful. I am trying to make a query that updates the [lastname] field
to just the lastname and no suffix.

Previous attempts:
Left$([lastname],InStr(1,[lastname]," ")-1)

Any suggestions

 

[Diesel]

This produced the same result
Right("[tblA_RECORD]![LASTNAME]",InStr([tblA_RECORD]![LASTNAME]," "))

Dear Diesel:

Is there always a space before the suffix you want to remove? Is
there ever a space within a last name without a suffix? What went
wrong using the code in your "Previous attempt"?

What you will need could be a custom solution. Your data, and the
challenges it presents, are likely to be unlike those of another
database. Often, this takes trial and error to determine the best
solution.

I was thinking you might want a table of possible suffixes for which
to search. That's just one approach, but it's a relatively powerful
one, while being a bit of work to implement. The specific compromise
between difficulty and accuracy that will be best for you is not
something we can prescribe from a distance.

If there are significantly large problems with your previous solution,
then knowing just what those problems are is key to being able to even
begin to suggest what the next step may be.

Get us some specific description of the challenges that remain and
likely someone can try to give you some suggestions.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

I am importing a text file which includes a field titled [lastname]. In some
of these cells the lastname has a suffix attached to it (e.g. Jr., IV, Sr.,
etc). I want an update query to be able to go through and parse the suffix
out of the last name. I have attempted several queries and have been
unsuccessful. I am trying to make a query that updates the [lastname] field
to just the lastname and no suffix.

Previous attempts:
Left$([lastname],InStr(1,[lastname]," ")-1)

Any suggestions

 

[Tom Ellison]

Dear Diesel:

I entered this into the immediate pane:

LEFT$("Smith Jr", INSTR(1, "Smith Jr", " ") - 1)

This should simulate what it is I think you intend to do. When it
returned the result of this function, it is:

"Smith"

The problem would be what it does when there is no space in a name.
For that reason, I have added to it:

LEFT$("Smith", IIF(INSTR(1, "Smith", " ") = 0, LEN("Smith"), INSTR(1,
"Smith", " ") - 1))

This returns "Smith" for "Smith" and "Smith" for "Smith Jr".

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

The result of that code produced the suffix staying in the lastname field.
As the lastname value I wanted to stay was erased. I tried reversing the
code but inserting Right$ instead of Left$ and it returned blank values
except for those values that included two words in the lastname field (eg.
Smith Jr.) showed up as "lastname]"

Dear Diesel:

Is there always a space before the suffix you want to remove? Is
there ever a space within a last name without a suffix? What went
wrong using the code in your "Previous attempt"?

What you will need could be a custom solution. Your data, and the
challenges it presents, are likely to be unlike those of another
database. Often, this takes trial and error to determine the best
solution.

I was thinking you might want a table of possible suffixes for which
to search. That's just one approach, but it's a relatively powerful
one, while being a bit of work to implement. The specific compromise
between difficulty and accuracy that will be best for you is not
something we can prescribe from a distance.

If there are significantly large problems with your previous solution,
then knowing just what those problems are is key to being able to even
begin to suggest what the next step may be.

Get us some specific description of the challenges that remain and
likely someone can try to give you some suggestions.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

I am importing a text file which includes a field titled [lastname]. In some
of these cells the lastname has a suffix attached to it (e.g. Jr., IV, Sr.,
etc). I want an update query to be able to go through and parse the suffix
out of the last name. I have attempted several queries and have been
unsuccessful. I am trying to make a query that updates the [lastname] field
to just the lastname and no suffix.

Previous attempts:
Left$([lastname],InStr(1,[lastname]," ")-1)

Any suggestions