calc field producing unwanted spaces amidst the final string

[kattatonic1]

Hello,



I am using a calculated field because the fiscal year of an important date will appear on many reports in the same format, and will be queried upon many times, etc.

E.g. dates from April 1, 2013 to March 31, 2014 should produce "13/14"

Here is my formula that works mighty fine:

IIf(Month([dtmXDate])>3,
Str(Year([dtmXDate])-2000) & "/" & Str(Year([dtmXDate])+1-2000),
Str(Year([dtmXDate])-1-2000) & "/" & Str(Year([dtmXDate])-2000))

EXCEPT it produces results like:
b13/b14
where the Bs are spaces

instead of my desired:
13/14

Any help would be appreciated. I have not developed anything for a few years and am updating my skills and the version knowledge slowing but Shirley. :-)

Regards, Kay

[ssanfu]

Try this:

IIf(Month([dtmXDate])>3,
Format([dtmXDate],"yy") & "/" & Format([dtmXDate],"yy")+1, 
Format([dtmXDate],"yy")-1 & "/" & Format([dtmXDate],"yy"))

or (uses the DateAdd() function)

IIf(Month([dtmXDate])>3,
Format([dtmXDate],"yy") & "/" & Format(DateAdd("yyyy",1,[dtmXDate]),"yy"),
Format(DateAdd("yyyy",-1,[dtmXDate]),"yy") & "/" & Format([dtmXDate],"yy"))

[kattatonic1]

Thanks, Steve, not a bad idea, but Format is not recognized in a calcalated field though.

Does anyone else have any ideas?

[rpeare]

use the TRIM function


IIf(Month([dtmXDate])>3,
Trim(Str(Year([dtmXDate])-2000)) & "/" & Trim(Str(Year([dtmXDate])+1-2000)),
Trim(Str(Year([dtmXDate])-1-2000)) & "/" & Trim(Str(Year([dtmXDate])-2000)))

[kattatonic1]

rpeare, You're a star! That was so obvious, no wonder I missed it.

Thanks! Works great.

Kay

[ssanfu]

but Format is not recognized in a calcalated field though.

Using a calculated field in a table is a bad ides.... I'm just saying.....

[kattatonic1]

Can I mark this as solved now, or does a moderator do that?

[ssanfu]

You do.