concatenate date formats with text please?

[mart10]

I am using CR XI

I want to run a crystal report every day to gather exchange rates and create a file ready for uploading in Oracle Financials

The busines rules are

FY 1 Apr 2009 to 31 Mar 2010 = FY 2010
FY 1 Apr 2010 to 31 Mar 2011 = FY 2011

etc


Running the report needs to create a particular filename according to above rules plus some preceeding fixed data and I can put this in the header or top group of the report:
Example:


For say 20th March this year I want:

a_Treasury_Actual09_Mar-2009_RR

and say 17th February next year I want:

a_Treasury_Actual10_Feb-2010_RR

In effect I want ' a_Treasury_ActualYY_MMM-YYYY_RR '

I just need a formula to do this

 

[hilfy]

Use the ToText formula to convert the date. It would look something like this:

'a_Treasury_Actual' + ToText({table.datefield}, 'yy_MMM-yyyy') + '_RR'

NOTE: The format string is Case Sensitive!

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[lbass]

You would need to adjust that a bit to get the correct fiscal year, as in:

'a_Treasury_Actual' + totext({table.datefield}+275,"='yy')+
ToText({table.datefield}, '_MMM-yyyy') + '_RR'

-LB

 

[mart10]

They want to run a crystal report every day to gather exchange rates and create a file ready for uploading in Oracle Financials

Their busines rules are

FY 1 Apr 2009 to 31 Mar 2010 = FY 2010
FY 1 Apr 2010 to 31 Mar 2011 = FY 2011

etc


Running the report needs to create a particular filename according to above rules plus some preceeding fixed data:

For 20th April this year they want:

a_Treasury_Actual10_Apr-2010_RR

but on say 20th March this year they want:

a_Treasury_Actual09_Mar-2009_RR

and say 17th February next year they want:

a_Treasury_Actual10_Feb-2010_RR

Would your above method work in this case and what about a leap year?

 

[lbass]

The method words for leap years, but I find it odd that they would specify the fiscal year and then restate it in the date--which I think confuses things. Are you sure that is their intent? The example I have trouble accepting is 10_Apr-2010--since the actual date is in 2009. I think it should be: 10_Apr-2009.

If you want to, you could replace the date field {table.date} with currentdate.

-LB

 

[mart10]

Yes its is their intent. If you look at the business rules they are saying 1st April this year is in fact 2010 but 31st is 2009. Its just the way they want it

many thanks

 

[mart10]

sorry the above I have tried and it doesnt work, It come back with error when trting to save the formuala:

The matching " for this string is missing

 

[mart10]

I have sorted via another method, thanks

 

[lbass]

Sorry, that should have been:

'a_Treasury_Actual' + totext({table.datefield}+275,'yy')+
ToText({table.datefield}, '_MMM-yyyy') + '_RR'

Why don't you explain what you finally used?

-LB

 

[mart10]

I created a couple of formula based on month. This was because found out needed parts of your above formula in different areas on body of report

thanks