Sort on a Query

[Bob]

My query has 3 fields:
[Payable] [Client] [ClientID]
I want to sort by [Payable] >1 [Client] Descending
So It will show the list as all clients with a total of more than $1 then
Clients alphabetical descending
Thanks for any help.............Bob







..........Jenny Vance

 

[Rick Brandt]

My query has 3 fields:
[Payable] [Client] [ClientID]
I want to sort by [Payable] >1 [Client] Descending
So It will show the list as all clients with a total of more than $1
then Clients alphabetical descending
Thanks for any help.............Bob

Add a calculated field to the query...

SortVal: IIf(Payable>1,0,1)

....then sort on that followed by Client.

Actually, wouldn't just sort on Payable descending (then Client) give you the
same sort?

 

[Smartin]

My query has 3 fields:
[Payable] [Client] [ClientID]
I want to sort by [Payable] >1 [Client] Descending
So It will show the list as all clients with a total of more than $1 then
Clients alphabetical descending
Thanks for any help.............Bob

.........Jenny Vance

Bob........ ........Jenny

Under [Payable] type in the Criteria row: >1
Under [Client] in the Sort row pick Descending

 

[Bob]

Rick only would if they owed all the same amount, what im wanting is the
alphabetical client in order of all that owe money...........Thanks Bob
I am getting a {Enter Parameter Value} on Value
have I got the wrong name this is the field Name:
Payable:
Nz(qTotalDueForPayment.TotalDue,0)-Nz(qPaidAmountForPayment.PaidTotal,0)

Thanks for your help...Bob

My query has 3 fields:
[Payable] [Client] [ClientID]
I want to sort by [Payable] >1 [Client] Descending
So It will show the list as all clients with a total of more than $1
then Clients alphabetical descending
Thanks for any help.............Bob

Add a calculated field to the query...

SortVal: IIf(Payable>1,0,1)

...then sort on that followed by Client.

Actually, wouldn't just sort on Payable descending (then Client) give you
the same sort?

 

[Rick Brandt]

Rick only would if they owed all the same amount, what im wanting is
the alphabetical client in order of all that owe
money...........Thanks Bob I am getting a {Enter Parameter Value} on
Value have I got the wrong name this is the field Name:
Payable:
Nz(qTotalDueForPayment.TotalDue,0)-Nz(qPaidAmountForPayment.PaidTotal,0)

You can't use a field name alias in other expressions of the same query. You
have to repeat the expression...

SortVal:
IIf((Nz(qTotalDueForPayment.TotalDue,0)-Nz(qPaidAmountForPayment.PaidTotal,0)
)>1,0,1)

That will wrap and look funny. Get it all back on one line then you should be
able to copy it.

 

[Bob]

One small problem with that I don't get any one with a credit at the bottom
of the list can I sort >1,<1,0
Thanks Bob

My query has 3 fields:
[Payable] [Client] [ClientID]
I want to sort by [Payable] >1 [Client] Descending
So It will show the list as all clients with a total of more than $1 then
Clients alphabetical descending
Thanks for any help.............Bob

.........Jenny Vance

Bob........ ........Jenny

Under [Payable] type in the Criteria row: >1
Under [Client] in the Sort row pick Descending

 

[Bob]

BRILLIANT thanks Rick worked great
Bob

You can't use a field name alias in other expressions of the same query.
You have to repeat the expression...

SortVal:
IIf((Nz(qTotalDueForPayment.TotalDue,0)-Nz(qPaidAmountForPayment.PaidTotal,0)
)>1,0,1)

That will wrap and look funny. Get it all back on one line then you
should be able to copy it.

 

[Smartin]

One small problem with that I don't get any one with a credit at the bottom
of the list can I sort >1,<1,0
Thanks Bob

My query has 3 fields:
[Payable] [Client] [ClientID]
I want to sort by [Payable] >1 [Client] Descending
So It will show the list as all clients with a total of more than $1 then
Clients alphabetical descending
Thanks for any help.............Bob

.........Jenny Vance

Bob........ ........Jenny

Under [Payable] type in the Criteria row: >1
Under [Client] in the Sort row pick Descending

Sorry about that. I realized after I posted it wasn't exactly what you
were looking for. I think Mr. Brandt has you covered though.

Cheers!