Sorting by an expression

[katemacdonald2]

Hi all,

I have created a query with 2 expressions in it, the second one depends on the first
Expression 1
interval: ([visit_date_m12]-[visit_date_scr])/365.25
Expression 2
perc_change: (([kmndbc_bsi_ml]/[brain_vol_scr])/[interval]*100)

The query works, but when I try and sort ascending by the second expression it asks me to specify a value for interval when I run the query.

Any ideas why this might happen when I try to sort?

many thanks,
Kate.

 

[vbaInet]

You can't sort on aliased fields. If you want to be able to sort on that field, you would need to create another query, pull in all the fields (plus the aliased fields) from that query and then sort using perc_change.

 

[DCrake]

Could try

perc_change: (([kmndbc_bsi_ml]/[brain_vol_scr])/(([visit_date_m12]-[visit_date_scr])/365.25)*100)

 

[Brianwarnock]

You can't sort on aliased fields. If you want to be able to sort on that field, you would need to create another query, pull in all the fields (plus the aliased fields) from that query and then sort using perc_change.

You can, its the alias in the calculation that throws it, hence DCrake's solution, ie replace the [interval] by ([visit_date_m12]-[visit_date_scr])/365.25
note that this need only be done in the orderby if you switch to SQL view.

SQL replaces 1st level Aliases by the code but not aliases in the code.

Brian

 

[vbaInet]

Hmm... I don't know where I got that from. Must have had myself wrapped up in Java land