Sort on Calculated Field in Report.

s_ojha

Board Regular
Joined
Oct 19, 2004
Messages
92
I have inserted expression/calculation as [A]++[C] in Total Field where [A], & [C] are fields formatted as Double with Fixed format and 02 decimal places in my Report. The problem is that I cannot sort on this calculated field. What I want is sort on this calculated field [Total] and incase of same number of two records then by [Date of Birth] field. i.e. I want to say ORDER BY [TOTAL],[Date of Birth].
 
Re: Sort on Calculated Field in Form.

I'm trying to sort on calculated field and get error message "Data type mismatch in criteria expression." I've tried sorting by alias, the entire calculation, and by ordinal number and all have same error - both in the query as well as using separate query to derive the calculation. Query runs fine until i try sorting on that field.

DiscETA = a date. Freetime = integer.


SELECT tblSPCBkngs.BkngNumber, tblSPCBkngs.Program, tblLocationMapping.CMA_PORT AS POD, tblSPCBkngs.DiscETA, DateAdd('d',[Freetime],[DiscETA]) AS [Last_Day_Freetime], tblSPCBkngs.DDA, tblSPCBkngs.FreeTime, tblLocationMapping_1.CMA_PORT AS DEST, tblPOD_Terms.Field1, tblBkngCntrList.CntrNumber

FROM (((tblSPCBkngs LEFT JOIN tblLocationMapping ON tblSPCBkngs.DiscPort = tblLocationMapping.APL_PORT) LEFT JOIN tblLocationMapping AS tblLocationMapping_1 ON tblSPCBkngs.Destination = tblLocationMapping_1.APL_PORT) LEFT JOIN tblPOD_Terms ON tblSPCBkngs.POD_Terms = tblPOD_Terms.Description) LEFT JOIN tblBkngCntrList ON tblSPCBkngs.BkngNumber = tblBkngCntrList.BkngNumber

WHERE (((tblSPCBkngs.Program)="SDDC") AND ((tblSPCBkngs.Cancelled_Hold)<>True And (tblSPCBkngs.Cancelled_Hold)<>Yes) AND ((tblSPCBkngs.Last_Cntr_Recd)<>True And (tblSPCBkngs.Last_Cntr_Recd)<>Yes))

ORDER BY DateAdd('d',[Freetime],[DiscETA]) AS [Last_Day_Freetime], tblSPCBkngs.DiscETA, tblSPCBkngs.BkngNumber;



and using separate query:

SELECT tblSPCBkngs.BkngNumber, tblSPCBkngs.Program, tblLocationMapping.CMA_PORT AS POD, tblSPCBkngs.DiscETA, qryLastDayFreetimeWorkaround.Last_Day_Freetime, tblSPCBkngs.DDA, tblSPCBkngs.FreeTime, tblLocationMapping_1.CMA_PORT AS DEST, tblPOD_Terms.Field1, tblBkngCntrList.CntrNumber

FROM ((((tblSPCBkngs LEFT JOIN tblLocationMapping ON tblSPCBkngs.DiscPort = tblLocationMapping.APL_PORT) LEFT JOIN tblLocationMapping AS tblLocationMapping_1 ON tblSPCBkngs.Destination = tblLocationMapping_1.APL_PORT) LEFT JOIN tblPOD_Terms ON tblSPCBkngs.POD_Terms = tblPOD_Terms.Description) LEFT JOIN tblBkngCntrList ON tblSPCBkngs.BkngNumber = tblBkngCntrList.BkngNumber) LEFT JOIN qryLastDayFreetimeWorkaround ON tblSPCBkngs.BkngNumber = qryLastDayFreetimeWorkaround.BkngNumber

WHERE (((tblSPCBkngs.Program)="SDDC") AND ((tblSPCBkngs.Cancelled_Hold)<>True And (tblSPCBkngs.Cancelled_Hold)<>Yes) AND ((tblSPCBkngs.Last_Cntr_Recd)<>True And (tblSPCBkngs.Last_Cntr_Recd)<>Yes))

ORDER BY qryLastDayFreetimeWorkaround.Last_Day_Freetime, tblSPCBkngs.DiscETA, tblSPCBkngs.BkngNumber;
 
Last edited:
Upvote 0

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Re: Sort on Calculated Field in Form.

If you look at the query without a Sort, do you have any errors (or unexpected values) in the calculated field you are trying to sort by?
That is probably the most likely cause of the error you are seeing.
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top