Sorting Question

G

Guest

Good Morning! I have the following SQL statement:

SELECT AqryFYTDFundSumHrs.LastName & ", " & AqryFYTDFundSumHrs.FirstName AS
Name, IIf([WorkHours]=0,0,NZ([SumDHours],0)/NZ([WorkHours],0)) AS DirPer,
AqryFYTDFundSumHrs.DirBugPer, NZ([DirPer])-NZ([DirBugPer]) AS DirDif,
AqryFYTDFundSumHrs.SumOfHours, NZ([SumAHours],0) AS AAHours,
NZ([SumOfHours]-[AAHours],0) AS WorkHours, NZ([SumIHours],0) AS OHHours,
IIf([WorkHours]=0,0,NZ([SumIHours],0)/NZ([WorkHours],0)) AS OHPerc,
NZ([SumDHours],0) AS DHours
FROM ((AqryFYTDFundSumHrs LEFT JOIN AqryFYTDFundSumHrsA ON
AqryFYTDFundSumHrs.PSUID = AqryFYTDFundSumHrsA.PSUID) LEFT JOIN
AqryFYTDFundSumHrsD ON AqryFYTDFundSumHrs.PSUID = AqryFYTDFundSumHrsD.PSUID)
LEFT JOIN AqryFYTDFundSumHrsI ON AqryFYTDFundSumHrs.PSUID =
AqryFYTDFundSumHrsI.PSUID
ORDER BY NZ([DirPer])-NZ([DirBugPer]) DESC;

I just added the ORDER BY clause to the end of it, however, when I try to
run it, I get the error "Enter Parameter Value" for DirPer. I woudl like to
sort on DirDif, descending. What am I doing wrong?

Appreciate the help!
 
G

Guest

Try removing the NZ function from DirPer in the Order By clause.

You could also try this:
ORDER BY 2 DESC;
 
G

Guest

Jerry,

The ORDER BY clause worked. Thanks for your help!

Leslie

Jerry Whittle said:
Try removing the NZ function from DirPer in the Order By clause.

You could also try this:
ORDER BY 2 DESC;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Leslie W. said:
Good Morning! I have the following SQL statement:

SELECT AqryFYTDFundSumHrs.LastName & ", " & AqryFYTDFundSumHrs.FirstName AS
Name, IIf([WorkHours]=0,0,NZ([SumDHours],0)/NZ([WorkHours],0)) AS DirPer,
AqryFYTDFundSumHrs.DirBugPer, NZ([DirPer])-NZ([DirBugPer]) AS DirDif,
AqryFYTDFundSumHrs.SumOfHours, NZ([SumAHours],0) AS AAHours,
NZ([SumOfHours]-[AAHours],0) AS WorkHours, NZ([SumIHours],0) AS OHHours,
IIf([WorkHours]=0,0,NZ([SumIHours],0)/NZ([WorkHours],0)) AS OHPerc,
NZ([SumDHours],0) AS DHours
FROM ((AqryFYTDFundSumHrs LEFT JOIN AqryFYTDFundSumHrsA ON
AqryFYTDFundSumHrs.PSUID = AqryFYTDFundSumHrsA.PSUID) LEFT JOIN
AqryFYTDFundSumHrsD ON AqryFYTDFundSumHrs.PSUID = AqryFYTDFundSumHrsD.PSUID)
LEFT JOIN AqryFYTDFundSumHrsI ON AqryFYTDFundSumHrs.PSUID =
AqryFYTDFundSumHrsI.PSUID
ORDER BY NZ([DirPer])-NZ([DirBugPer]) DESC;

I just added the ORDER BY clause to the end of it, however, when I try to
run it, I get the error "Enter Parameter Value" for DirPer. I woudl like to
sort on DirDif, descending. What am I doing wrong?

Appreciate the help!
Click to expand...
Click to expand...
 
Top