Ordering a report on function return string

[vicissitude]

Hi all,

Is it possible to order a report based on a field which has a long string value which has been returned from a function?

The function returns a date along with information relevant to that date as a string.

Is it possible to extract the date i.e. left(strReturn,9) and then order by that date?

Appreciate any help.

 

[vbaInet]

Yes you can. Just call the function in the Query and in there select the sort order.

 

[vicissitude]

Thanks for the help!

Managed to get it working. If you extract part of the string and then put it in a query field and then try to set ascending then it won't work but if you set orderby in the query settings it seems to be ok with it.

But i found that the better way was to extract the date from the string using

OrderDate: CDate(Left([Review Due],9)) setting this to a new query field

and then using 'sorting and grouping' on report to sort on that and other fields at the same time.

Many thanks again. This has been a stumbling block for a while.

 

[vbaInet]

Oh yes, I forgot you were talking about a report. That's certainly the way to go.

Good job!