Splitting a year range like 92-95 to show 92 93 94 95 1

[jeffm777]

I have a field in a database that currently shows, for example, the following:

92-95 AEROSTAR (FORD VAN)

or

99 BUICK LESABRE

If the field contains like 92-95, I need a formula to convert the years to show 92 93 94 95 and extract them so only the years show without the make/model info.

 

[briangriffin]

Do all the records with a year range cut off the first digit? In your first post we were using 92-95 AEROSTAR (FORD VAN), and I thought it was working with that value.

And since I don't have any other ideas at the moment, are you sure it's not something like the field isn't wide enough?

 

[jeffm777]

I figured it out - brain fart on my end. It's working fine. I do have one question though...will this work on future vehicle years? For example, if a year range spans, say 07-12 or 99-11, will the years show 07 08 09 10 11 12, or 99 00 01 02 03 04 05 06 07 08 09 10 11?

 

[briangriffin]

Been there many times.

You're right about the future years - it would have broken starting with 2010. This will work for the next 40 years:

// First extract only the numbers.

whileprintingrecords;
stringvar v_newcar := "";
numbervar v_counter := 1;

while isnumeric({Sheet1_.Values}[v_counter]) or {Sheet1_.Values}[v_counter] = "-"
do

(if isnumeric({Sheet1_.Values}[v_counter])
then
v_newcar := v_newcar + {Sheet1_.Values}[v_counter]
else v_newcar;
v_counter := v_counter + 1);

//////////////////////////////////////////////

// Next create an array and split the numbers into two digit values.

stringvar array v_array := [left(v_newcar,2),""];
numbervar v_counter2 := 3;


while v_counter2 <= len(v_newcar)
do

(v_array[v_counter2 - 1] := mid(v_newcar,v_counter2,v_counter2 + 1);
redim preserve v_array[ubound(v_array) + 1];
v_counter2 := v_counter2 + 2);

/////////////////////////////////////////////////
// Convert the years to four digits.

numbervar v_anothercounter := 1;
while v_anothercounter < ubound(v_array)
do

(if left(v_array[v_anothercounter],1) in ["0","1","2","3","4"] then v_array[v_anothercounter] := "20" + v_array[v_anothercounter]
else v_array[v_anothercounter] := "19" + v_array[v_anothercounter];
v_anothercounter := v_anothercounter + 1);


/////////////////////////////////////////////////
// Last create another array and populate it with the min, max, and all values in between.

numbervar v_min := tonumber(v_array[1]);
numbervar v_max := tonumber(v_array[ubound(v_array) - 1]);
stringvar array v_display := [totext(v_min,0)];
numbervar v_counter3 := 2;
numbervar v_counter4 := v_min;


while v_counter4 < v_max
do

(redim preserve v_display[v_counter3];
v_display[v_counter3] := totext(v_counter4 + 1,0);
v_counter3 := v_counter3 + 1;
v_counter4 := v_counter4 + 1);


numbervar v_loop :=1;
while v_loop <= ubound(v_display)
do

(v_display[v_loop] := right(v_display[v_loop],2);
v_loop := v_loop + 1);

if ubound(v_display) = 2 then v_display[1]
else
join(v_display," ");

For my own benefit, I'd like to figure out a way to display just the years for the records that look like:

98 AUDI A4; 99 AUDI A4 ;02 AUDI S4

So keep an eye on this thread and if I get it working I'll post in the next day or two.

 

[jeffm777]

Will do. Thanks!!!!!

 

[jeffm777]

Hey Brian. Have you had a chance to think any more about how to display just the years for the records that look like:

98 AUDI A4; 99 AUDI A4 ;02 AUDI A4

It turns out that I do need this for the report. Tks.

 

[briangriffin]

I almost had it working but not quite. I'll revisit it as soon as I have some time, but it might take a day or two.

 

[jeffm777]

That's fine. Thanks!!!!

 

[briangriffin]

It was too much of a pain to get everything working in one formula, so I ended up with three custom functions and one formula that calls all three.

This assumes that discrete values are always separated with a semicolon, and range values are always separated with a dash.

If you haven't used custom functions before, you can access them in the formula workshop - right click, New.

First one - called ProcessDiscreteValues

Function (stringVar LocalValues);
Local stringVar array DiscreteValues := Split (LocalValues, ";");
Local numberVar i;

For i := 1 to UBound (DiscreteValues) Do
    DiscreteValues[i] := Left (DiscreteValues[i], 2)
;
LocalValues := Join (DiscreteValues,", ");

Second one - called ProcessRangeOfValues

Function (stringVar LocalValues);

Local stringVar MinString := LocalValues[1 to 2];
Local stringVar MaxString := LocalValues[4 to 5];

Local numberVar MinYear;
Local numberVar MaxYear;
Local numberVar NumberOfYears;
Local numberVar i;
Local stringVar array YearsArray;



If MinString[1] in ["0" to "4"] Then
    MinString := "20"  & MinString
Else
    MinString := "19"  & MinString;

If MaxString[1] in ["0" to "4"] Then
    MaxString := "20"  & MaxString
Else
    MaxString := "19"  & MaxString;

MinYear := ToNumber (MinString);
MaxYear := ToNumber (MaxString);

NumberOfYears := MaxYear - MinYear + 1;

redim  YearsArray[NumberOfYears];

For i := 1 to NumberOfYears Do
    YearsArray[i] := Right (ToText ((MinYear + i - 1), 0, ""), 2)
;
LocalValues := Join (YearsArray, ", ");

Third one - called ProcessSingleValue

Function (stringvar LocalValue);
LocalValue := LocalValue[1 to 2];

Last, create a formula to return the formatted values:

Local stringVar Values := {YourDBField};
//
//  Get rid of embedded spaces
//
    Values := Replace (Values, " ", "");
//
//  Now decide whether to treat Values for multiple discrete values,
//  a range, or a single discrete value
//
If InStr (Values, ";") > 0 Then
    ProcessDiscreteValues (Values)
Else If InStr (Values, "-") > 0 Then
   ProcessRangeOfValues (Values)
Else
    ProcessSingleValue (Values);

You only have to change the formula to your database field, the functions can't reference any database field.