Dates as text 4

[KBChristy]

I have the following fields which I have converted to give me the month and the year.

formula= Year({SOP_Order_Line.Date_Required})

dim a as number, b as string
If isdate(toText({SOP_Order_Line.Date_Required})) then
a = datepart("m",{SOP_Order_Line.Date_Required})
If a > 0 and a < 13 then
formula = MonthName (a,true)
else
formula = &quot;Unknown&quot;
end if
else
formula = &quot;Unknown&quot;
end if


I want to be able to concatenate them together so that I get Jan 2003 / Apr 2003 etc. I have used the following formula to do this but keep getting Jan 2,003.00 / Apr 2,003.00. How do I get Jan 2003 etc please?

{@Month} & &quot; &quot; & {@Year}

 

[Reebo99]

The easiest formula to use would be :

ToText({SOP_Order_Line.Date_Required},&quot;MMM yyyy&quot

This would eliminate all of your code.

The reason your code was returning 2,003.00 is, when converting numbers to text, i.e. 2003, you must specify decimal places and thousand separator as :

Totext(2003,0,&quot;&quot

0 = no deciaml places
&quot;&quot; = no thousand separator.

It also looks like you are using Basic Syntax. When using my formula, ensure you switch to Crystal Syntax.

Hope this helps.


Reebo
Scotland (Sunny with a Smile)

 

[Reebo99]

The easiest formula to use would be :

ToText({SOP_Order_Line.Date_Required},&quot;MMM yyyy&quot

This would eliminate all of your code.

The reason your code was returning 2,003.00 is, when converting numbers to text, i.e. 2003, you must specify decimal places and thousand separator as :

Totext(2003,0,&quot;&quot

0 = no decimal places
&quot;&quot; = no thousand separator.

It also looks like you are using Basic Syntax. When using my formula, ensure you switch to Crystal Syntax.

Hope this helps.


Reebo
Scotland (Sunny with a Smile)

 

[synapsevampire]

You can always right click a date and select Format Field->Date/Time->Customize->Date and change the Month, Day and Year to whatever you want without code, and the field remains a date rather than being converted to a string.

Neither the original post nor the solution offered addressed the concatenation of the dates.

-k

 

[Reebo99]

SV,

I'm pretty sure the concatenation was purely the 2 formulas KBChristy had created to show @month and @year. The 2 dates in the original question was just 2 separate examples.

Reebo
Scotland (Sunny with a Smile)

 

[synapsevampire]

Reebo:

Just right click the date field

({SOP_Order_Line.Date_Required})

and you can format it identically to what your totext does, and you don't alter the data type.

Which eliminates all of your code

-k

 

[Reebo99]

I would have thought you could have given me enough benefit of the doubt by now to know I fully understand how to format a field for display.

The question posted was regarding doing the same thing through a formula. I was trying to show the best way to do this, and also explain why KBChristy was not getting the expected results through his/her formula.

By the way, have you been voted top tipster 2 weeks running? or has it not been updated yet? if it's the former, congrats again!

Reebo
Scotland (Sunny with a Smile)

 

[synapsevampire]

Yeah, I knew that you understood this, I was just being playful, Reebo.

I've no idea about the tipster, and I'm not sure whether it's a badge of success, or a sign of poor time management...Thanks though. Always good to give back to the community, everybody here seems very strong and I learn something daily.

-k

 

[joanneabator]

The funny thing about this problem for us, in one report the dates are correct; such as 09/2003 and in another (which uses the exact same formula as the first) they come out 9.00/2,003.00. The only difference appears to be one machine launching the report is NT vs one running XP (XP gets it right). Does anyone know of any switches -- be they Crystal, Access or Windows -- that might impact the totext(month)+&quot;/&quot;+totext(Year) formula?

 

[Reebo99]

The ToText() function uses the local number format specified as part of the operating system.

An explanation, goto your regional options within windows nt under control panel. Under the numbers tab there will be a number of decimal places and the thousand separator. If these are set to &quot;2&quot; and &quot;,&quot; respectively, then ToText(2003) will result in 2,003.00 being displayed.

If you set the decimal places to 0 , ToText(2003) will result in 2,003

I'm pretty sure you can't remove the thousand separator.

Please Note... I am NOT recommending you change these values. If you require a specific format within Crystal Reports, then keep the changes within Crystal Reports.

Reebo
UK