Find existing ranges of an alphanumeric string field 1

[rradelet]

From an Access table of Postal Codes, I am trying to run a report in Crystal 11 that will display the minimum and maximum ranges of the postal codes, but not include any invalid ranges in the min & max.

Example:

{Postal Code}

V6M0A1
V6M1A1
V6M1A2
V6M1A3
V6M1A4
V6M1A5
V6M1A6
V6M1A7
V6M1A8
V6M1A9
V6M1B2
V6M1B3
V6M1B4
V6M1B7
V6M1B8
V6M1B9
V6M1C1
V6M1C2
V6M1C3
V6M1C4
V6M1C5
V6M1C7
V6M1C8
V6M1C9

The result required is:

V6M0A1 to V6M0A1
V6M1A1 to V6M1A9
V6M1B2 to V6M1B4
V6M1B7 to V6M1B9
V6M1C1 to V6M1C5
V6M1C7 to V6M1C9

I have tried grouping on a formula:

Left ({POSTAL_CODE},5 )

and then using min & max summaries but the resulting ranges then include
values that don’t exist:

V6M0A1 to V6M0A1
V6M1A1 to V6M1A9
V6M1B2 to V6M1B9
V6M1C1 to V6M1C9

Can anyone steer me in the right direction?

 

[Madawc]

I don't understand why the following are viewed by you as two different groups:

V6M1B2
V6M1B3
V6M1B4

V6M1B7
V6M1B8
V6M1B9

They have the first five characters the same. If you want to separate them you could do a formula field that hard-codes such differences. Or if there is some other field, combine the two in a formula field and use that to group.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

If you just want to display the existing ranges, you can do the following:

Create this formula {@accum} for the detail section and suppress it:
whileprintingrecords;
stringvar minrange;
stringvar maxrange;
stringvar rangex;

if onfirstrecord or
(
left({table.postalcode},5) = left(previous({table.postalcode}),5) and
{table.postalcode} <> previous({table.postalcode}) + 1
) then
minrange := {table.postalcode} else
minrange := minrange;
if (
onlastrecord or
(
left({table.postalcode},5) = left({table.postalcode},5) and
right({table.postalcode},1) <> right(next({table.postalcode}),1)-1
)
) and
(
onfirstrecord or
(
left({table.postalcode},5) = left({table.postalcode},5) and
right({table.postalcode},1) > right(previous({table.postalcode}),1)
)
) then
maxrange := {table.postalcode} else
maxrange := 0;
if maxrange <> 0 then
rangex := rangex + minrange + " to " + maxrange + chr(13);

Then place {@display} in the report footer:
whileprintingrecords;
stringvar rangex;

Be sure to format it to "can grow". If you need each range in a separate field, you could split the display formula, using chr(13). Let me know.

-LB

 

[rradelet]

lbass, Thanks for the reply.

I hadn't used the previous or next functions before, but I think I understand how they work.

I tried your formula, but can't get it to work:

1) After line #9

"{table.postalcode} <> previous({table.postalcode}) + 1"

I receive a "A string is required here" error


2) The data that I am trying to create the ranges for has over 150,000 records, so there will be too many characters
to display as a string in the report footer. What I originally posted as the required result was incorrect. What I actually need is just 2 columns:

Min Max
V6M0A1 V6M0A1
V6M1A1 V6M1A9
V6M1B2 V6M1B4
V6M1B7 V6M1B9
V6M1C1 V6M1C5
V6M1C7 V6M1C9

 

[lbass]

That should have been:

whileprintingrecords;
stringvar minrange;
stringvar maxrange;
stringvar rangex;

if onfirstrecord or
(
left({table.postalcode},5) = left(previous({table.postalcode}),5) and
val(right({table.postalcode},1)) <> val(right(previous({table.postalcode}),1)) + 1
) then
minrange := {table.postalcode} else
minrange := minrange;
if (
onlastrecord or
(
left({table.postalcode},5) = left({table.postalcode},5) and
val(right({table.postalcode},1)) <> val(right(next({table.postalcode}),1))-1
)
) and
(
onfirstrecord or
(
left({table.postalcode},5) = left({table.postalcode},5) and
val(right({table.postalcode},1)) > val(right(previous({table.postalcode}),1))
)
) then
maxrange := {table.postalcode} else
maxrange := 0;
if maxrange <> 0 then
rangex := rangex + minrange + " to " + maxrange + chr(13);

On your second point, you will not have 150000 ranges, only one range everytime there is a break in the sequence. I'm not sure what the limit is for the length of one string, but it's pretty high. How are you then going to use the results--that would have an impact, also.

-LB

 

[rradelet]

lbass:

"A string is required here" error now occurs at:

maxrange := 0;
if maxrange <> 0 then


I tried changing these to:

maxrange := "";
if maxrange <> "" then

and can now run the report, but the Min values aren't right

For example:

V6B0A1
V6B0A2
V6B0A3
V6B0A4
V6B0A5
V6B0A6
V6B0A7
V6B0A8

V6B0B1
V6B0B2

V6B0B4
V6B0B5
V6B0B6
V6B0B7
V6B0B8
V6B0B9

V6B0C1
V6B0C2
V6B0C3
V6B0C4
V6B0C5

V6B0C7
V6B0C8
V6B0C9



returns

V6B0A1 to V6B0A8
V6B0A1 to V6B0B2
V6B0B4 to V6B0B9
V6B0B4 to V6B0C5
V6B0C7 to V6B0B9
V6B0C7 to V6B0E9



The correct ranges should be:

V6B0A1 to V6B0A8
V6B0B1 to V6B0B2
V6B0B4 to V6B0B9
V6B0C1 to V6B0C5
V6B0C7 to V6B0C9

 

[lbass]

Sorry, I tested this on a number field, and then didn't translate everything back to a string. Before I try to work on this any further, would this solution work for you if I fix the minrange issue? Or is another approach necessary?

-LB

 

[lbass]

The following worked for me with a string field:

whileprintingrecords;
stringvar minrange;
stringvar maxrange;
stringvar rangex;

if onfirstrecord or
[red]left({table.postalcode},5) <> left(previous({table.postalcode}),5) or [/red]
(
left({table.postalcode},5) = left(previous({table.postalcode}),5) and
val(right({table.postalcode},1)) <> val(right(previous({table.postalcode}),1)) + 1
) then
minrange := {table.postalcode}; [red]note deletion [/red]
if (
onlastrecord or
(
left({table.postalcode},5) = left({table.postalcode},5) and
val(right({table.postalcode},1)) <> val(right(next({table.postalcode}),1))-1
)
) and
(
onfirstrecord or
(
left({table.postalcode},5) = left({table.postalcode},5) and
val(right({table.postalcode},1)) > val(right(previous({table.postalcode}),1))
)
) then
maxrange := {table.postalcode} else
maxrange := [red]""[/red];
if maxrange <> [red]""[/red] then
rangex := rangex + minrange + " to " + maxrange + chr(13);

-LB

 

[rradelet]

LB:

The new formulas give the correct result, but the maximum length for a string field is 65534 characters.

Breaking the selection down into small chunks works, but I what I am trying to do is to export the min & max records as 2 different fields into a new Access table,or and Excel or CSV file.

I am trying to update a the postal code data in an address verification application.

Thanks,

RR

 

[lbass]

So you need to have the min and max of each range in a separate row, while the above shows all ranges in one string. Arrays might work--if you have less that 1000 ranges. Can you clarify?

-LB

 

[rradelet]

LB:

There are probably too many records for an array.

In the table, the distinct count of

left({table.postalcode},5)

is over 14,000. There is a city field in the table, so the
export could be done in several runs, selecting by city or range of city names, but the distinct count of

left({table.postalcode},5)

for the largest city is over 2,000

RR