Array or Subreport or .........?

[JasonGreen]

Basically, my problem is that I have data that is all bunched together that I need to massage and format so that I can use it in the Group Expert on my report.

In the database, I have company records that have a memo type field for the location of their subsidiaries. The subsidiary data was entered in as State Abbreviation SPACE City ENTER.
So the data looks like this:

CompanyName:
CompanyA

Subsidiaries:
AZ Phoenix
AZ Scottsdale
FL Jacksonville
FL Orlando

CompanyName:
CompanyB

Subsidiaries:
AZ Phoenix
CT Wallingford
DC Washington

What I am trying to do is to Group by the State then by the City. I am currently replacing the Carriage return with a character combination that wont be found elsewhere in the data, then splitting on that to create and array. Then I loop through that array and split on the space in between the state and city. Problem is that this is creating a string of all of the states for that company only where I need them to somehow be combined. See my code below.

dim i as number
dim strLocation as string
strLocation = {vwNACEJCIndexDetails.OppByLocation}
dim strarrLocation() as string
dim strarrState() as string
dim strOutput as string

strLocation = Replace(strLocation, chr(13) + chr(10), "!~!")

strarrLocation = Split(strLocation, "!~!")

if Ubound(strarrLocation) >= 1 then
for i = 1 to ubound(strarrLocation)
strarrState = Split(strarrLocation(i), " ")
strOutput = strOutput + strarrState(1)
next
else
strOutput = strOutput + strLocation
end if

formula = strOutput

Any ideas?

 

[lbass]

Which is it? {tblCityState.City} or {JCCityState.City}?

I don't really think I can troubleshoot this from here. Sorry.

-LB

 

[JasonGreen]

It's JCCityState now. I renamed it.

Ok, I appreciate all your help.