Sorting with parameter fields of type date, number and string

hi, i have added a parameter to my report so i can choose what to sort by.
I have managed to sort it by creating a formula with a switch function to change according to the parameter the fields its supposed to sort by. the only problem is i need to sort it by different field types.
How can i do this?
i managed sorting by date and number types, i convert the date field to numbers without "/" and it sorts perfectly. however the string (table.Name) cant be converted to number. If i convert all to String type, the numbers as string will not sort properly (for ex. 100.1 will go before 15)
what can i do so i can sort with all types?
thanks
LVL 1
carlosmonteAsked:
Who is Participating?
 
mlmccConnect With a Mentor Commented:
Try this idea to convert the numbers

CSTR({YourField},"0000000000.00000")

That should put 0's on the front of the string

Another idea is

Right("0000000000" &CStr({YourField},4,''),15)

mlmcc
0
 
carlosmonteAuthor Commented:
sorry, but i dont know how this would help me.
can you explain? will this way make sorting possible for all db fields like Date, numbers and strings?
0
 
mlmccCommented:
If you convert numbers into strings of the same length and add leading 0's then then sort correctly.

Your example of 100.1 and 15  becomes
000000015.00000
000000100.10000

Thus it sorts correctly.  You simply use them for the sorting and use the real field for display.

mmlcc

0
 
carlosmonteAuthor Commented:
works great, nice little trick.
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.