Cutting a string and adding a number to it

[slickp007]

Ok i have a reference which for every year roles over ie

A0745058 will next year become A0845058

I want to report on the year before current reference, so i need to cut the 2nd and 3rd characters from the string, -1 and then add it back to the string, so taking the example above A0745058 will become A0645058.

Any ideas on how to do this, taking into account that in 2010 the above example will look like this A1045058

 

[AndyMc]

slickp007,

This formula should work:

//@reference
numbervar yr := val(mid({table.field},2,2)) - 1;
left({table.field},1) & totext(yr,"00") & mid({table.field},4)

Andy

 

[CR85user]

I was taking the same approach but using two seperate formulae to give a little more flexibility if you decide to change the way you look at the data:

//{@currentyear}
right(totext(year(currentdate)),2)

//{@fieldyear)
mid({table.reference},2,2)


Then use a selection criteria of:

val({@fieldyear}) = val({@currentyear})-1


This then will allow you to query those year references in different selection formulas in future without much alteration.

'J

 

[slickp007]

works wicked, cheers mate