Sort on Calculated Field in Report.

[s_ojha]

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].

 

[bravura]

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;

 

[Joe4]

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.