Trim text before or after a comma

[Guest]

How do I change a field from "Last Name, First Name" to "Last Name" only?

 

[fredg]

How do I change a field from "Last Name, First Name" to "Last Name" only?

LastName:Left(FullName],InStr([FullName],",")-1)

and, while you didn't ask....

FirstName:Mid([FullName],InStr([FullName],",")+2)

 

[Guest]

In a query, you would do something like:

Last_Name: LEFT([NameField], IIF(instr([NameField], ",")>1,
instrr([NameField], ",") - 1, LEN([NameField])

If you are certain that every occurance of your [NameField] contains the
comma, you could simply do:

Last_Name: LEFT([NameField], instr([NameField], ",") - 1)

However, both of these use the LEFT function, which will generate an error
if the value in you [NameField] is NULL, so you might want to consider:

Last_Name: Switch(ISNULL([NameField], [NameField], instr([NameField], ",") >
1, LEFT([NameField], instr([NameField], ",") - 1, TRUE, [NameField])

The Switch( ) function accepts pairs of parameters, where the first element
of each pair is a logical statement that will evaluate to True or False. The
second element is the value that will be returned if the first part evaluates
to TRUE. In my example above, my first test is to determine whether the
[NameField] is null, if so, I just return the [NameField], but you could put
something in here like "Error: Name is missing". The second tests whether
there is a comma at position two or greater; if so, it strips everything to
the left of the comma. The last test TRUE, will always evaluate to TRUE and
is the fall back position if no comma is found.

HTH
Dale

 

[Guest]

Thank you. That worked great.

In a query, you would do something like:

Last_Name: LEFT([NameField], IIF(instr([NameField], ",")>1,
instrr([NameField], ",") - 1, LEN([NameField])

If you are certain that every occurance of your [NameField] contains the
comma, you could simply do:

Last_Name: LEFT([NameField], instr([NameField], ",") - 1)

However, both of these use the LEFT function, which will generate an error
if the value in you [NameField] is NULL, so you might want to consider:

Last_Name: Switch(ISNULL([NameField], [NameField], instr([NameField], ",") >
1, LEFT([NameField], instr([NameField], ",") - 1, TRUE, [NameField])

The Switch( ) function accepts pairs of parameters, where the first element
of each pair is a logical statement that will evaluate to True or False. The
second element is the value that will be returned if the first part evaluates
to TRUE. In my example above, my first test is to determine whether the
[NameField] is null, if so, I just return the [NameField], but you could put
something in here like "Error: Name is missing". The second tests whether
there is a comma at position two or greater; if so, it strips everything to
the left of the comma. The last test TRUE, will always evaluate to TRUE and
is the fall back position if no comma is found.

HTH
Dale



--
Email address is not valid.
Please reply to newsgroup only.

How do I change a field from "Last Name, First Name" to "Last Name" only?

 

[duet76]

Thank you. That worked great.

In a query, you would do something like:

Last_Name: LEFT([NameField], IIF(instr([NameField], ",")>1,
instrr([NameField], ",") - 1, LEN([NameField])

If you are certain that every occurance of your [NameField] contains the
comma, you could simply do:

Last_Name: LEFT([NameField], instr([NameField], ",") - 1)

However, both of these use the LEFT function, which will generate an error
if the value in you [NameField] is NULL, so you might want to consider:

Last_Name: Switch(ISNULL([NameField], [NameField], instr([NameField], ",") >
1, LEFT([NameField], instr([NameField], ",") - 1, TRUE, [NameField])

The Switch( ) function accepts pairs of parameters, where the first element
of each pair is a logical statement that will evaluate to True or False. The
second element is the value that will be returned if the first part evaluates
to TRUE. In my example above, my first test is to determine whether the
[NameField] is null, if so, I just return the [NameField], but you could put
something in here like "Error: Name is missing". The second tests whether
there is a comma at position two or greater; if so, it strips everything to
the left of the comma. The last test TRUE, will always evaluate to TRUE and
is the fall back position if no comma is found.

HTH
Dale

"lman" wrote:

- Show quoted text -

I am attempting to trim after a comma. I need to plan for blank
fields and for fields with no comma. I am attempting to use the
switch command posted. I can't find my error. I have replaced my
field names to be appropriate to my database. I have this formula in
the CARAssigedTo field of my query.

Switch(ISNULL([CARAssignedTo], [CARAssignedTo], instr([CARAssignedTo],
",") > 1, LEFT([CARAssignedTo], instr([CARAssignedTo], ",") - 1, TRUE,
[CARAssignedTo])

When I run the query I get the following error: The expression you
entered has a function containing the wrong number of arguments.

Does anyone see the error of my ways?

TIA,

Juliet M

 

[Dale Fye]

Juliet,

You left out a ) at the end of the LEFT() function. Try:

Switch(ISNULL([CARAssignedTo], [CARAssignedTo],

instr([CARAssignedTo],",") > 1, LEFT([CARAssignedTo],
instr([CARAssignedTo], ",") - 1),
TRUE, [CARAssignedTo])

Thank you. That worked great.

In a query, you would do something like:

Last_Name: LEFT([NameField], IIF(instr([NameField], ",")>1,
instrr([NameField], ",") - 1, LEN([NameField])

If you are certain that every occurance of your [NameField] contains
the
comma, you could simply do:

Last_Name: LEFT([NameField], instr([NameField], ",") - 1)

However, both of these use the LEFT function, which will generate an
error
if the value in you [NameField] is NULL, so you might want to consider:

Last_Name: Switch(ISNULL([NameField], [NameField], instr([NameField],
",") >
1, LEFT([NameField], instr([NameField], ",") - 1, TRUE, [NameField])

The Switch( ) function accepts pairs of parameters, where the first
element
of each pair is a logical statement that will evaluate to True or
False. The
second element is the value that will be returned if the first part
evaluates
to TRUE. In my example above, my first test is to determine whether
the
[NameField] is null, if so, I just return the [NameField], but you
could put
something in here like "Error: Name is missing". The second tests
whether
there is a comma at position two or greater; if so, it strips
everything to
the left of the comma. The last test TRUE, will always evaluate to
TRUE and
is the fall back position if no comma is found.

HTH
Dale

"lman" wrote:

How do I change a field from "Last Name, First Name" to "Last Name"
only?- Hide quoted text -

- Show quoted text -

I am attempting to trim after a comma. I need to plan for blank
fields and for fields with no comma. I am attempting to use the
switch command posted. I can't find my error. I have replaced my
field names to be appropriate to my database. I have this formula in
the CARAssigedTo field of my query.

Switch(ISNULL([CARAssignedTo], [CARAssignedTo], instr([CARAssignedTo],
",") > 1, LEFT([CARAssignedTo], instr([CARAssignedTo], ",") - 1, TRUE,
[CARAssignedTo])

When I run the query I get the following error: The expression you
entered has a function containing the wrong number of arguments.

Does anyone see the error of my ways?

TIA,

Juliet M