Date Formatting Problem

[huddy33]

I have a date field pulling from parameters, showing the date as:

May 5 - May 6, 2011

I want to remove the month from the second field, if it matches the month of the first field. I have tried something along the lines of if (month {enddate})<>(month{startdate}) then '', but I get errors asking for the result to be in number form.

Any ideas on how I can make it look like this:

May 5-6, 2011, using a formula? I don't want to remove the month if the event is something like March 31 - April 2, 2011, you know?

 

[briangriffin]

if month(minimum({?My Parameter})) <> month(maximum({?My Parameter}))

then

totext(minimum({?My Parameter}),"MMMM d, yyyy") + " - " + totext(maximum({?My Parameter}),"MMMM d, yyyy")

else

totext(minimum({?My Parameter}),"MMMM d") +" -" + totext(maximum({?My Parameter}),"d, yyyy")

I left the year in on the first condition in case you cross years - December 31, 2011 - January 1, 2012.

 

[huddy33]

Ugh, I'm sorry, Brian. I made a mistake in the original post. The dates I'm formatting are just regular date fields, not parameters. My parameter for this report is an eventID. Sorry for the confusion.

In the event that your code would still work, I tried it, replacing it like below and I still get the same error, asking for a number result. Any ideas?

if month(minimum({Event.StartDate})) <> month(maximum({Event.EndDate}))

then

totext(minimum({Event.StartDate}),"MMMM d, yyyy") + " - " + totext(maximum({Event.EndDate}),"MMMM d, yyyy")

else

totext(minimum({Event.StartDate}),"MMMM d") +" -" + totext(maximum({Event.EndDate}),"d, yyyy")

 

[briangriffin]

It should probably still work, so first can you verify the datatype of the Event.StartDate field, and also what is your source database (Oracle, SQL Server, Excel, etc.)?

Then put this formula in the detail section:

month({Event.StartDate})

If that works (returns a number 1-12), then replace it with this:

totext({Event.StartDate},"MM/dd/yyyy")

And post the results.

 

[huddy33]

It's a DateTime field and the datasource is SQL. The formula returns a number like 5.00. When I put the second formula in the report, it returns 05/05/2011.

Thanks!

 

[briangriffin]

Try this:

whileprintingrecords;

datetimevar v_min := date(2011,1,1);
datetimevar v_max := date(2011,2,2);


if month(v_min) <> month(v_max)

then

totext(v_min,"MMMM d, yyyy") + " - " + totext(v_max,"MMMM d, yyyy")

else

totext(v_min,"MMMM d") +" -" + totext(v_max,"d, yyyy")

That should work fine, and changing the value of v_max to date(2011,1,2) will change the display.

The remaining task is to change the variables to the min/max of the field. If the StartDate and EndDate are at the record level and that's what you want then just change them to:

datetimevar v_min := {Event.StartDate};
datetimevar v_max := {Event.EndDate};

That seems unlikely, so if you want the min/max dates for the entire report it would be:

datetimevar v_min := minimum({Event.StartDate});
datetimevar v_max := maximum({Event.StartDate});

Lastly, if you wanted this computed at the group level, it would be:

datetimevar v_min := minimum({Event.StartDate},GROUPNAMEHERE);
datetimevar v_max := maximum({Event.StartDate},GROUPNAMEHERE);

 

[lbass]

huddy33,

Where are you creating the following formula? It should work, so I think you are getting the error message because you are creating it in the wrong formula area. You should be creating it in the field explorer->formula->new.

if month(minimum({Event.StartDate})) <> month(maximum({Event.EndDate}))then
totext(minimum({Event.StartDate}),"MMMM d, yyyy") + " - " +
totext(maximum({Event.EndDate}),"MMMM d, yyyy")else
totext(minimum({Event.StartDate}),"MMMM d") +" - +
totext(maximum({Event.EndDate}),"d, yyyy")

-LB

 

[huddy33]

Thank you both for your help. BrianGriffin, I think using a variable was too complicated for me. I was unable to make it work.

LBass, you were right. I was trying to use the format text conditional area to make this happen. As soon as I made it a formula instead, I got the results I wanted.

Thank you both!