How to split the field?

[bloomlight]

I have a field looks like this:

BCCH, ROOM#1
BCCH, ROOM#123
BCCH, ROOM#523

OHHCO, APT#6

MAWESTTOWN, FLOOR3, ROOM 302
MAWESTTOWN, FLOOR7, ROOM 732

What I need is to show 1st part of the filed to the report. Then sub-count and Total-count the 1st part. In another words, the report would be like this:

BCCH
BCCH
BCCH
BCCH SUB-TOTAL 3

OHHCO
OHHCO SUB-TOTAL 1

MAWESTTOWN
MAWESTTOWN
MAWESTTOWN SUB-TOTAL 2

ALL-TOTAL 6

Any idea how to split a field, then display to the report?

Thanks in advance.

 

[hilfy]

Try this:

1. Create a formula that looks something like this:

Split({MyTable.MyField}, ',', 1)

This will get the first part of the field based on where the first comma is located.

2. Group on this formula.
3. Put the formula in the details section.
4. In the group footer, add a summary that is a count of a field that is unique to each record.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[bloomlight]

I have tried: Split({MyTable.MyField}, ',', 1)

But got the error message saying: The result of a formula cannot be an array. Did I miss anything?

 

[lbass]

If not isnull({MyTable.MyField}) then
Split({MyTable.MyField}, ',')[1]

-LB