Query, Change SortVal

[Bob V]

SortVal:
IIf((Nz([qTotalDueForPayment].[TotalDue],0)-Nz([qPaidAmountForPayment].[PaidTotal],0))0,>1,1)
This Code is sorting in this order
$20
$0
-$20

How can I change it to this order
$20
-$20
$0
So as zero balance will be last to show

Thanks for any Help........Bob

 

[Tom van Stiphout]

I might add another column, which displays True or False depending on
if the SortVal is 0 or not:
(IIf((Nz([qTotalDueForPayment].[TotalDue],0)-Nz([qPaidAmountForPayment].[PaidTotal],0))0,>1,1)=0)

Then sort by that column first (I'm sure you can figure out Ascending
or Descending), and by SortVal next.

-Tom.

 

[Bob V]

Tom I am getting a Invalid Syntax error on The second to last Zero 0,
Thanks Bob

I might add another column, which displays True or False depending on
if the SortVal is 0 or not:
(IIf((Nz([qTotalDueForPayment].[TotalDue],0)-Nz([qPaidAmountForPayment].[PaidTotal],0))0,>1,1)=0)

Then sort by that column first (I'm sure you can figure out Ascending
or Descending), and by SortVal next.

-Tom.

SortVal:
IIf((Nz([qTotalDueForPayment].[TotalDue],0)-Nz([qPaidAmountForPayment].[PaidTotal],0))0,>1,1)
This Code is sorting in this order
$20
$0
-$20

How can I change it to this order
$20
-$20
$0
So as zero balance will be last to show

Thanks for any Help........Bob

 

[Tom van Stiphout]

Perhaps that's because your original expression has a syntax error the
way it appeared in your message. What I tried to write was:
(YourExpression=0)
which is a boolean expression testing if your expression is 0.
Just past your working expression in the place of YourExpression.

-Tom.

Tom I am getting a Invalid Syntax error on The second to last Zero 0,
Thanks Bob

I might add another column, which displays True or False depending on
if the SortVal is 0 or not:
(IIf((Nz([qTotalDueForPayment].[TotalDue],0)-Nz([qPaidAmountForPayment].[PaidTotal],0))0,>1,1)=0)

Then sort by that column first (I'm sure you can figure out Ascending
or Descending), and by SortVal next.

-Tom.

SortVal:
IIf((Nz([qTotalDueForPayment].[TotalDue],0)-Nz([qPaidAmountForPayment].[PaidTotal],0))0,>1,1)
This Code is sorting in this order
$20
$0
-$20

How can I change it to this order
$20
-$20
$0
So as zero balance will be last to show

Thanks for any Help........Bob

 

[Bob V]

Hope this is what you wanted?............Thanks Bob
SELECT tblOwnerInfo.OwnerID,
IIf((Nz([qTotalDueForPayment].[TotalDue],0)-Nz([qPaidAmountForPayment].[PaidTotal],0))>1,0,1)
AS SortVal, IIf(Not IsNull(),">") AS EmailAlert1,
IIf(IsNull(tblOwnerInfo.OwnerLastName),'',tblOwnerInfo.OwnerLastName & ' ')
& IIf(IsNull(tblOwnerInfo.OwnerFirstName),'',tblOwnerInfo.OwnerFirstName) AS
Name, qTotalDueForPayment.TotalDue AS DueAmount,
qPaidAmountForPayment.PaidTotal AS Paid,
Nz(qTotalDueForPayment.TotalDue,0)-Nz(qPaidAmountForPayment.PaidTotal,0) AS
Payable,
IIf(IsNull([tblOwnerInfo].[EmailAlert]),'',[tblOwnerInfo].[EmailAlert]) AS
EmailAlert
FROM (tblOwnerInfo LEFT JOIN qPaidAmountForPayment ON tblOwnerInfo.OwnerID =
qPaidAmountForPayment.OwnerID) LEFT JOIN qTotalDueForPayment ON
tblOwnerInfo.OwnerID = qTotalDueForPayment.OwnerID
WHERE (((tblOwnerInfo.Status) Like 'Active*'))
ORDER BY
IIf((Nz([qTotalDueForPayment].[TotalDue],0)-Nz([qPaidAmountForPayment].[PaidTotal],0))>1,0,1),
IIf(IsNull(tblOwnerInfo.OwnerLastName),'',tblOwnerInfo.OwnerLastName & ' ')
& IIf(IsNull(tblOwnerInfo.OwnerFirstName),'',tblOwnerInfo.OwnerFirstName);

[QUOTE="Tom van Stiphout"]
Perhaps that's because your original expression has a syntax error the
way it appeared in your message. What I tried to write was:
(YourExpression=0)
which is a boolean expression testing if your expression is 0.
Just past your working expression in the place of YourExpression.

-Tom.


[QUOTE]
Tom I am getting a Invalid Syntax error on The second to last Zero 0,
Thanks Bob

[QUOTE="Tom van Stiphout"]
I might add another column, which displays True or False depending on
if the SortVal is 0 or not:
(IIf((Nz([qTotalDueForPayment].[TotalDue],0)-Nz([qPaidAmountForPayment].[PaidTotal],0))0,>1,1)=0)

Then sort by that column first (I'm sure you can figure out Ascending
or Descending), and by SortVal next.

-Tom.





SortVal:
IIf((Nz([qTotalDueForPayment].[TotalDue],0)-Nz([qPaidAmountForPayment].[PaidTotal],0))0,>1,1)
This Code is sorting in this order
$20
$0
-$20

How can I change it to this order
$20
-$20
$0
So as zero balance will be last to show

Thanks for any Help........Bob
[/QUOTE]
[/QUOTE][/QUOTE]