How to change a sort with different values

[johnism]

Does any body know how I can do this, what I am doing is changing a group through a
Formula running of a parameter. Now everything works find except that if one of my outputs is of a different format. For example ( if sort =a then string, else if sort=b then string, else if sort=c then date) Crystal will want the last to be a string all so (so I can convert the date to a string by to text(date field) the only problem is the date sort is all jacked up then cause it is sorting it by text value not date value.
If I put the date as the first part same problem but in reverse wants strings to be dates.
This is my formula


if {?SortA}="T" then {Tkscale.VehicleID}
else if {?SortA}="H" then {Tkscale.CarrierID}
else if {?SortA}="P" then {Tkscale.ProductID}
else if {?SortA}="D" then {Aptruck.LastLoadDate}
else if {?SortA}="Ti" then {Aptruck.LastLoadTime}

Even if I was able to get date to work then I run into the same problem with the time field.
So how can I do a sort with different data values , that runs off a parameter value?
Any Ideas.
Thanks,
John

2000 machine, Crystal 8 in case any body though that might make a difference.

 

[smcnulty2000]

There isn't an easy way that I know of. There are some ways to get through this though. The trick is to return a string, but make that string be sortable in the same exact order that it would be if it were a date or time.

I've had a lot of luck on this kind of problem with the datepart formula. This uses the form:

datepart(interval type,datefield). For example.

datepart("wwww",{@StartDate})

So you could try this:
{@DateFormula}
datepart("yyyy",{Aptruck.LastLoadDate}) &" "& datepart("y",{Aptruck.LastLoadDate})

Then change your main formula like this:

if {?SortA}="T" then {Tkscale.VehicleID}
else if {?SortA}="H" then {Tkscale.CarrierID}
else if {?SortA}="P" then {Tkscale.ProductID}
else if {?SortA}="D" then {@DateFormula}
else if {?SortA}="Ti" then {Aptruck.LastLoadTime}

A formula like this will get you your sort order and should return a string. You would then do something similar for the {Aptruck.LastLoadTime} time. The listings for what you can derive from datepart can be found in the help file by searching for "datepart".

You will, however, have to alter the @DateFormula so that it puts the appropriate number of zeros in front of the day number or it still won't sort correctly.


{@DateFormula}
Select datepart("y",{Aptruck.LastLoadDate})

Case 0 to 9:
datepart("yyyy",{Aptruck.LastLoadDate}) &" 00"& datepart("y",{Aptruck.LastLoadDate})

Case 10 to 99:
datepart("yyyy",{Aptruck.LastLoadDate}) &" 0"& datepart("y",{Aptruck.LastLoadDate})

Default :
datepart("yyyy",{Aptruck.LastLoadDate}) &" "& datepart("y",{Aptruck.LastLoadDate})

This should always return a string that would be like "2001 012" for January 12, 2001.

Anyway, that's my best guess. If you want to display the real date, you have to "Customize Group Name Field" in the change group expert and you would need to build a second formula there that translates the strings back into dates or something similar. You can add a string to the front of the formula given above that triggers the conversion process and passes anything else through.

For example, taking the last part of the above formula but you would add this to all parts:

Default :
"date " & datepart("yyyy",{Aptruck.LastLoadDate}) &" "& datepart("y",{Aptruck.LastLoadDate})

Which would return a string like "date 2001 012"

Then in your group name formula you do a test like this:

If {@formula}[1 to 4]= "date" then
{Aptruck.LastLoadDate}
else
{@formula}

I won't spell it out further because I don't really have the time to go through the rest of it right now.

As usual, I too would prefer to see an easier way if someone has one.

 

[kenhamady]

Use:

ToText({Orders.Order Date},"yyyy-MM-dd&quot

For the full explanation see my FAQ on common formula examples in the FAQ area, formula number 4 I believe. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[smcnulty2000]

Thanks Ken.