Unable to Sort on calculated field, based on another calculated field (1 Viewer)

[JohnPapa]

I have the following RecordSource

SELECT dbo_tblManagementCommitteeInstance.intID, dbo_tblManagementCommitteeInstance.intManagementCommittee, dbo_tblManagementCommitteeInstance.dtmFrom,
dbo_tblManagementCommitteeInstance.dtmTo, dbo_tblManagementCommitteeInstance.bitD, dbo_tblManagementCommitteeInstance.dtmD, dbo_tblManagementCommitteeInstance.nvcLUC,
dbo_tblManagementCommitteeInstance.intD, dbo_tblIe.nvcName, dbo_tblIe.intMCTerm, dbo_tblIe.intMCTermAlert, DateAdd("m",[intMCTerm],[dtmFrom]) AS dtmExpiry,
DateAdd("m",-[intMCTermAlert],[dtmExpiry]) AS dtmExpiryAlert, DateDiff("d",Date(),[dtmExpiry]) AS intExpiryDay
FROM dbo_tblIe LEFT JOIN dbo_tblManagementCommitteeInstance ON dbo_tblIe.intID = dbo_tblManagementCommitteeInstance.intManagementCommittee
WHERE (((dbo_tblManagementCommitteeInstance.bitD)=False)) ;

and would like to Sort on calculated field intExpiryDay.

When I try to use

ORDER BY intExpiryDay

it is prompting me to enter a value for Parameter "dtmExpiry", which is used in intExpiryDay

DateDiff("d",Date(),[dtmExpiry]) AS intExpiryDay

 

[ebs17]

The order of query execution is not from front to back, but has its own rules.

To shorten it: When sorting, the calculated field has not yet been named, so the given alias intExpiryDay is not yet known. This is why the parameter demand also occurs.

At this point you would have to repeat the calculation expression:

...
ORDER BY DateDiff("d",Date(),[dtmExpiry])

 

[JohnPapa]

The order of query execution is not from front to back, but has its own rules.

To shorten it: When sorting, the calculated field has not yet been named, so the given alias intExpiryDay is not yet known. This is why the parameter demand also occurs.

At this point you would have to repeat the calculation expression:

...
ORDER BY DateDiff("d",Date(),[dtmExpiry])

I had already tried your suggestion and it tells me "Syntax error in ORDER BY clause".

BTW, the BE is SQL Server.

 

[ebs17]

For Jet-SQL the expression shown is correct.

Where exactly is the query being executed? T-SQL obviously requires a slightly different syntax.

 

[CJ_London]

think ebd17 meant

ORDER BY DateDiff("d",Date(),DateAdd("m",[intMCTerm],[dtmFrom]))

since [dtmExpiry] is also a calculated value

 

[ebs17]

is also a calculated value

Thanks. I had overlooked that.

 

[JohnPapa]

think ebd17 meant

ORDER BY DateDiff("d",Date(),DateAdd("m",[intMCTerm],[dtmFrom]))

since [dtmExpiry] is also a calculated value

Indeed that solved the problem.
Many thanks.

 

[Josef P.]

Alternative: Sort by column number:

SELECT
   dbo_tblManagementCommitteeInstance.intID,
   dbo_tblManagementCommitteeInstance.intManagementCommittee,
   dbo_tblManagementCommitteeInstance.dtmFrom,
   dbo_tblManagementCommitteeInstance.dtmTo,
   dbo_tblManagementCommitteeInstance.bitD,
   dbo_tblManagementCommitteeInstance.dtmD,
   dbo_tblManagementCommitteeInstance.nvcLUC,
   dbo_tblManagementCommitteeInstance.intD,
   dbo_tblIe.nvcName,
   dbo_tblIe.intMCTerm,
   dbo_tblIe.intMCTermAlert,
   DateAdd("m",[intMCTerm],[dtmFrom]) AS dtmExpiry,
   DateAdd("m",-[intMCTermAlert],[dtmExpiry]) AS dtmExpiryAlert,
   DateDiff("d",Date(),[dtmExpiry]) AS intExpiryDay
FROM
   dbo_tblIe LEFT JOIN dbo_tblManagementCommitteeInstance ON dbo_tblIe.intID = dbo_tblManagementCommitteeInstance.intManagementCommittee
WHERE
   (((dbo_tblManagementCommitteeInstance.bitD)=False))
Order By
    14

 

[Gasman]

Thanks. I had overlooked that.

Well if you think you had to replace a calculated value with the calculation for one, you would need to do the same for any others?

 

[ebs17]

Yes, depending on the location of use.
Filtering in the WHERE clause and then grouping take place before sorting and therefore cannot use a column alias that is created in the SELECT part.

 

[JohnPapa]

Alternative: Sort by column number:

SELECT
   dbo_tblManagementCommitteeInstance.intID,
   dbo_tblManagementCommitteeInstance.intManagementCommittee,
   dbo_tblManagementCommitteeInstance.dtmFrom,
   dbo_tblManagementCommitteeInstance.dtmTo,
   dbo_tblManagementCommitteeInstance.bitD,
   dbo_tblManagementCommitteeInstance.dtmD,
   dbo_tblManagementCommitteeInstance.nvcLUC,
   dbo_tblManagementCommitteeInstance.intD,
   dbo_tblIe.nvcName,
   dbo_tblIe.intMCTerm,
   dbo_tblIe.intMCTermAlert,
   DateAdd("m",[intMCTerm],[dtmFrom]) AS dtmExpiry,
   DateAdd("m",-[intMCTermAlert],[dtmExpiry]) AS dtmExpiryAlert,
   DateDiff("d",Date(),[dtmExpiry]) AS intExpiryDay
FROM
   dbo_tblIe LEFT JOIN dbo_tblManagementCommitteeInstance ON dbo_tblIe.intID = dbo_tblManagementCommitteeInstance.intManagementCommittee
WHERE
   (((dbo_tblManagementCommitteeInstance.bitD)=False))
Order By
    14

I can confirm that this works as well. Did not know that you could just insert the column number like that.