Avatar of Mark Wilson
Mark Wilson
 asked on

Totext and year

I am using the following formuas for headings in my report

iif(month(today)=1,"Jan - " & totext((year(currentdate)-1),0,""),"Jan - " & totext((year(currentdate)),0,""))

i.e. the example is the jan heading, if the month of the current date is 1 then heading should be Jan + last year i.e. Jan-2011 else month plus this year i.e. Jan-2012

Is there a better way of doing this and is there a way of getting the year to 2 digts so it will diplay as Jan-11 or Jan-12?

Thanks
Crystal Reports

Avatar of undefined
Last Comment
James0628

8/22/2022 - Mon
Ess Kay

you can set it as current date

 then right click it in the report

select format

and select the option MAR-99

as seen in the picture


demo

hope that helps
Mike McCracken

You could do something like

To get Jan last year
CStr(DateAdd('yyyy',-1,CurrentDate),'MMM-yy')

Convert a date to MON-YY

CStr(CurrentDate,'MMM-yy')

ToText(CurrentDate,'MMM-yy')

mlmcc
ASKER CERTIFIED SOLUTION
James0628

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Ess Kay

i think my way is simpler, and may be quicker, since there is no calculation going on

its simply a formatting function.


what it does is just reformat the entire field
if you need to change year, you can do that in the formula itself, and it will still be quicker than the other option

the solution below, has 3 calculations,
1> if statement  -assess the field, and decides which option to do
2> pull the year from the equation
3> convert it to text




also, it only handles January, not EVERY Month

iif(month(today)=1,"Jan - " & Right (totext((year(currentdate)-1),0,""), 2),"Jan - " & Right (totext((year(currentdate)),0,""), 2))

Open in new window

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
James0628

esskayb2d,

 The problem (as I see it) with your suggestion is that you're only producing the current month.  If the current month is January, 2012, you get Jan-12 (which is not what the OP wants), or Jan-11 (which is correct) if you use a formula to subtract 1 from the year.  But if the current month is May, 2012, for example, you'd get May-12, and that's not what the OP wants.  If the month is not Jan, they want "Jan-YY", with the current year in "YY".

 My guess is that this is something like a financial report, with a separate column for each month.  So, this column will always be for January, regardless of the current month.  They're just changing the year.  If the current month is Jan, the heading shows Jan of last year.  Otherwise, it shows Jan of this year.

 Like you said, it only handles January, but I think that's the point.  I'm guessing there are similar formulas for the other months, each in a separate column.

 James
Ess Kay

so, a new report file for each month of the year?


makes no sence

one report can handle all months using different parameters


make a parameter for marchs, and the formatting formula will change the data to the correct format. you dont need to use 'current date' in the formula


the accepted way is regardless of the month in question, it will always be changed to 'Jan'
James0628

No, not a new report for each month.  Just "fixed" columns (eg. for the fiscal year).  The report is not run for a single month.  It has columns for all of the months in the year, or all of the months from the beginning of the year up to today, or something like that.  One place where you might use something like that formula would be if the report was for the previous 12 months.  Run the report today, and one month ago is Apr-12, two months ago is Mar-12, etc.  12 months ago would be last May, so that would be May-11, instead of May-12.

 Of course this is all just guesswork on my part, based on what the OP was doing in that formula, and reports I've seen that had columns similar to that.

 James
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.