Deleting part of a string

  • Thread starter Thread starter John Harrington
  • Start date Start date
J

John Harrington

I have a field in a report that is a comma delimited list.

What I'd like is a function that will delete all after the first
comma, so that I get the first value only.

For example "Fred, Barney, Wilma" becomes "Fred".

In other words, I'd like whatever the VBA equivalent is of:

s/,.*//

If "Replace" is the fxn I need, I'm not sure how to make it do this.


Thanks in advance,
John
 
I have a field in a report that is a comma delimited list.

What I'd like is a function that will delete all after the first
comma, so that I get the first value only.

For example "Fred, Barney, Wilma" becomes "Fred".

In other words, I'd like whatever the VBA equivalent is of:

s/,.*//

If "Replace" is the fxn I need, I'm not sure how to make it do this.

Thanks in advance,
John
Click to expand...

In the report, use an unbound text control.
Set it's control source to:
=Left([FieldName],InStr([FieldName],",")-1)
 
I would make a small change to that suggestion to handle the cases of only one
name or zero length strings "" or nulls.

Note the concatenation of a comma to [FieldName] value in the InStr function.

Left([FieldName],InStr([FieldName] & ",",",")-1)

Examples:
Instr(Null,",") will return null and that will cause an error in the LEFT
function.

Instr("",",") returns 0 and subtracting 1 from zero returns -1 and again that
causes an error in the LEFT function.

Instr("John",",") returns zero (see above)


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I have a field in a report that is a comma delimited list.

What I'd like is a function that will delete all after the first
comma, so that I get the first value only.

For example "Fred, Barney, Wilma" becomes "Fred".

In other words, I'd like whatever the VBA equivalent is of:

s/,.*//

If "Replace" is the fxn I need, I'm not sure how to make it do this.

Thanks in advance,
John
Click to expand...

In the report, use an unbound text control.
Set it's control source to:
=Left([FieldName],InStr([FieldName],",")-1)
Click to expand...
 
Back
Top