Query wont sort ?

Any idea why this wont sort by Lastname ?

SELECT C.Restitution-NZ(P.TotalPaid,0) AS [Current Debt], Trim(C.Firstname &
" " & C.Lastname) AS [Debtor's Name],
IIf(DisputedSW=True,"Disputed","Undisputed") AS [Type of Debt],
IIf(P.PayAge30,'X','') AS [Age 30 Days], IIf(P.PayAge=30 And
P.PayAge=90,'X','') AS [Age 30-90 Days], IIf(P.PayAge90,'X','') AS [Age 90
Days], IIf(NZ(HomeAddressStreet,'')='','',HomeAddressStre et & ', ' &
HomeAddressCity & ', ' & HomeAddressState & ' ' & HomeAddressZip) AS
[Debtor's Address], C.PID AS [Debtor's Personal ID], C.DOB AS [Debtor's Date
of Birth], C.Phone AS [Other Debtor Contact Data], C.CaseNumber AS [Agency
Debt Identifier], C.Comments3 AS [Collection Efforts to Date]
FROM [Beth AFirms] AS C LEFT JOIN qryCasePayments AS P ON
C.CaseNumber=P.CaseNumber
WHERE C.Unit = 'Member Fraud' AND (C.Restitution-NZ(P.TotalPaid,0))0
ORDER BY C.Lastname;

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

Do you get an error message? Does it sort in the wrong order?

Is this the source for a report and the records in the report are not sorted?

If that is the case, a query sort has no direct relationship to how the
records are sorted for a report. Open the report in design view and select
View: Sorting and grouping and set up the sort for the report using the dialog.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Dorian wrote:
Any idea why this wont sort by Lastname ?

SELECT C.Restitution-NZ(P.TotalPaid,0) AS [Current Debt], Trim(C.Firstname &
" " & C.Lastname) AS [Debtor's Name],
IIf(DisputedSW=True,"Disputed","Undisputed") AS [Type of Debt],
IIf(P.PayAge30,'X','') AS [Age 30 Days], IIf(P.PayAge=30 And
P.PayAge=90,'X','') AS [Age 30-90 Days], IIf(P.PayAge90,'X','') AS [Age 90
Days], IIf(NZ(HomeAddressStreet,'')='','',HomeAddressStre et & ', ' &
HomeAddressCity & ', ' & HomeAddressState & ' ' & HomeAddressZip) AS
[Debtor's Address], C.PID AS [Debtor's Personal ID], C.DOB AS [Debtor's Date
of Birth], C.Phone AS [Other Debtor Contact Data], C.CaseNumber AS [Agency
Debt Identifier], C.Comments3 AS [Collection Efforts to Date]
FROM [Beth AFirms] AS C LEFT JOIN qryCasePayments AS P ON
C.CaseNumber=P.CaseNumber
WHERE C.Unit = 'Member Fraud' AND (C.Restitution-NZ(P.TotalPaid,0))0
ORDER BY C.Lastname;

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

On Wed, 17 Feb 2010 16:06:10 -0800, Dorian
wrote:

Any idea why this wont sort by Lastname ?

What's it doing instead? Are you looking at the sort order on a Report? If so,
you need to specify the sort in the Report's Sorting and Grouping dialog - it
ignores the sort order of the Recordsource.

If you open the query as a query datasheet, what does it give you?
--

John W. Vinson [MVP]

No report involved, am just opening query in database window and see results
in datasheet. Order appears random.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


"John Spencer" wrote:

Do you get an error message? Does it sort in the wrong order?

Is this the source for a report and the records in the report are not sorted?

If that is the case, a query sort has no direct relationship to how the
records are sorted for a report. Open the report in design view and select
View: Sorting and grouping and set up the sort for the report using the dialog.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Dorian wrote:
Any idea why this wont sort by Lastname ?

SELECT C.Restitution-NZ(P.TotalPaid,0) AS [Current Debt], Trim(C.Firstname &
" " & C.Lastname) AS [Debtor's Name],
IIf(DisputedSW=True,"Disputed","Undisputed") AS [Type of Debt],
IIf(P.PayAge30,'X','') AS [Age 30 Days], IIf(P.PayAge=30 And
P.PayAge=90,'X','') AS [Age 30-90 Days], IIf(P.PayAge90,'X','') AS [Age 90
Days], IIf(NZ(HomeAddressStreet,'')='','',HomeAddressStre et & ', ' &
HomeAddressCity & ', ' & HomeAddressState & ' ' & HomeAddressZip) AS
[Debtor's Address], C.PID AS [Debtor's Personal ID], C.DOB AS [Debtor's Date
of Birth], C.Phone AS [Other Debtor Contact Data], C.CaseNumber AS [Agency
Debt Identifier], C.Comments3 AS [Collection Efforts to Date]
FROM [Beth AFirms] AS C LEFT JOIN qryCasePayments AS P ON
C.CaseNumber=P.CaseNumber
WHERE C.Unit = 'Member Fraud' AND (C.Restitution-NZ(P.TotalPaid,0))0
ORDER BY C.Lastname;

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
.

No report involved, am just opening query in database window and see results
in datasheet. Order appears random.
This is Access 2003.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


"John W. Vinson" wrote:

On Wed, 17 Feb 2010 16:06:10 -0800, Dorian
wrote:

Any idea why this wont sort by Lastname ?

What's it doing instead? Are you looking at the sort order on a Report? If so,
you need to specify the sort in the Report's Sorting and Grouping dialog - it
ignores the sort order of the Recordsource.

If you open the query as a query datasheet, what does it give you?
--

John W. Vinson [MVP]
.

Technically, it is ok to ORDER on a field we don't SELECT, but what happens
if you add that field:

SELECT C.Lastname, C.Restitution-NZ...
....
ORDER BY C.Lastname


Also note that ' and - near the begining of a string are consider invisible
character as sorting is involved (from a Win32 based convention)

Xfile
X-files
Xfilet


is correctly ordered ascendingly.



Vanderghast, Access MVP


"Dorian" wrote in message
...
Any idea why this wont sort by Lastname ?

SELECT C.Restitution-NZ(P.TotalPaid,0) AS [Current Debt], Trim(C.Firstname
&
" " & C.Lastname) AS [Debtor's Name],
IIf(DisputedSW=True,"Disputed","Undisputed") AS [Type of Debt],
IIf(P.PayAge30,'X','') AS [Age 30 Days], IIf(P.PayAge=30 And
P.PayAge=90,'X','') AS [Age 30-90 Days], IIf(P.PayAge90,'X','') AS [Age
90
Days], IIf(NZ(HomeAddressStreet,'')='','',HomeAddressStre et & ', ' &
HomeAddressCity & ', ' & HomeAddressState & ' ' & HomeAddressZip) AS
[Debtor's Address], C.PID AS [Debtor's Personal ID], C.DOB AS [Debtor's
Date
of Birth], C.Phone AS [Other Debtor Contact Data], C.CaseNumber AS [Agency
Debt Identifier], C.Comments3 AS [Collection Efforts to Date]
FROM [Beth AFirms] AS C LEFT JOIN qryCasePayments AS P ON
C.CaseNumber=P.CaseNumber
WHERE C.Unit = 'Member Fraud' AND (C.Restitution-NZ(P.TotalPaid,0))0
ORDER BY C.Lastname;

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and
they
eat for a lifetime".

On Wed, 17 Feb 2010 16:06:10 -0800, Dorian
wrote:

Any idea why this wont sort by Lastname ?

SELECT C.Restitution-NZ(P.TotalPaid,0) AS [Current Debt], Trim(C.Firstname &
" " & C.Lastname) AS [Debtor's Name],
IIf(DisputedSW=True,"Disputed","Undisputed") AS [Type of Debt],
IIf(P.PayAge30,'X','') AS [Age 30 Days], IIf(P.PayAge=30 And
P.PayAge=90,'X','') AS [Age 30-90 Days], IIf(P.PayAge90,'X','') AS [Age 90
Days], IIf(NZ(HomeAddressStreet,'')='','',HomeAddressStre et & ', ' &
HomeAddressCity & ', ' & HomeAddressState & ' ' & HomeAddressZip) AS
[Debtor's Address], C.PID AS [Debtor's Personal ID], C.DOB AS [Debtor's Date
of Birth], C.Phone AS [Other Debtor Contact Data], C.CaseNumber AS [Agency
Debt Identifier], C.Comments3 AS [Collection Efforts to Date]
FROM [Beth AFirms] AS C LEFT JOIN qryCasePayments AS P ON
C.CaseNumber=P.CaseNumber
WHERE C.Unit = 'Member Fraud' AND (C.Restitution-NZ(P.TotalPaid,0))0
ORDER BY C.Lastname;

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

It may be a corrupt Index on LastName. Try opening the table [Beth AFirms] in
design view and select the Lastname field. Does it have an index?

Or... shudder... did you make it a Lookup field?? That would sort by the ID,
not by the text name.
--

John W. Vinson [MVP]

My best guess at this point -
[Beth AFirms].LastName is a LOOKUP field in table design. If it is then you
are sorting by a number that indicates a record in another related table that
contains the actual data.

If you have used the Lookup field, the way to fix the query is to include the
table that contains the actual data in your query and sort by the field in the
table with the actual data.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Dorian wrote:
No report involved, am just opening query in database window and see results
in datasheet. Order appears random.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


"John Spencer" wrote:

Do you get an error message? Does it sort in the wrong order?

Is this the source for a report and the records in the report are not sorted?

If that is the case, a query sort has no direct relationship to how the
records are sorted for a report. Open the report in design view and select
View: Sorting and grouping and set up the sort for the report using the dialog.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Dorian wrote:
Any idea why this wont sort by Lastname ?

SELECT C.Restitution-NZ(P.TotalPaid,0) AS [Current Debt], Trim(C.Firstname &
" " & C.Lastname) AS [Debtor's Name],
IIf(DisputedSW=True,"Disputed","Undisputed") AS [Type of Debt],
IIf(P.PayAge30,'X','') AS [Age 30 Days], IIf(P.PayAge=30 And
P.PayAge=90,'X','') AS [Age 30-90 Days], IIf(P.PayAge90,'X','') AS [Age 90
Days], IIf(NZ(HomeAddressStreet,'')='','',HomeAddressStre et & ', ' &
HomeAddressCity & ', ' & HomeAddressState & ' ' & HomeAddressZip) AS
[Debtor's Address], C.PID AS [Debtor's Personal ID], C.DOB AS [Debtor's Date
of Birth], C.Phone AS [Other Debtor Contact Data], C.CaseNumber AS [Agency
Debt Identifier], C.Comments3 AS [Collection Efforts to Date]
FROM [Beth AFirms] AS C LEFT JOIN qryCasePayments AS P ON
C.CaseNumber=P.CaseNumber
WHERE C.Unit = 'Member Fraud' AND (C.Restitution-NZ(P.TotalPaid,0))0
ORDER BY C.Lastname;

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
.

No lookup field involved. Lastname is Text(50).
Tried all suggestions. None worked.
However, created a new query and copied in SQL text and ran it.
It worked.
So same SQL text in two different queries gives different results !
Something messed up the original query.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


"John Spencer" wrote:

My best guess at this point -
[Beth AFirms].LastName is a LOOKUP field in table design. If it is then you
are sorting by a number that indicates a record in another related table that
contains the actual data.

If you have used the Lookup field, the way to fix the query is to include the
table that contains the actual data in your query and sort by the field in the
table with the actual data.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Dorian wrote:
No report involved, am just opening query in database window and see results
in datasheet. Order appears random.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


"John Spencer" wrote:

Do you get an error message? Does it sort in the wrong order?

Is this the source for a report and the records in the report are not sorted?

If that is the case, a query sort has no direct relationship to how the
records are sorted for a report. Open the report in design view and select
View: Sorting and grouping and set up the sort for the report using the dialog.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Dorian wrote:
Any idea why this wont sort by Lastname ?

SELECT C.Restitution-NZ(P.TotalPaid,0) AS [Current Debt], Trim(C.Firstname &
" " & C.Lastname) AS [Debtor's Name],
IIf(DisputedSW=True,"Disputed","Undisputed") AS [Type of Debt],
IIf(P.PayAge30,'X','') AS [Age 30 Days], IIf(P.PayAge=30 And
P.PayAge=90,'X','') AS [Age 30-90 Days], IIf(P.PayAge90,'X','') AS [Age 90
Days], IIf(NZ(HomeAddressStreet,'')='','',HomeAddressStre et & ', ' &
HomeAddressCity & ', ' & HomeAddressState & ' ' & HomeAddressZip) AS
[Debtor's Address], C.PID AS [Debtor's Personal ID], C.DOB AS [Debtor's Date
of Birth], C.Phone AS [Other Debtor Contact Data], C.CaseNumber AS [Agency
Debt Identifier], C.Comments3 AS [Collection Efforts to Date]
FROM [Beth AFirms] AS C LEFT JOIN qryCasePayments AS P ON
C.CaseNumber=P.CaseNumber
WHERE C.Unit = 'Member Fraud' AND (C.Restitution-NZ(P.TotalPaid,0))0
ORDER BY C.Lastname;

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
.

.

No lookup field involved. Lastname is Text(50), no index on it.
Tried all suggestions. Nothing worked.
However, created a new query and copied in SQL text and ran it.
It worked.
So same SQL text in two different queries gives different results !
Something messed up the original query.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


"John W. Vinson" wrote:

On Wed, 17 Feb 2010 16:06:10 -0800, Dorian
wrote:

Any idea why this wont sort by Lastname ?

SELECT C.Restitution-NZ(P.TotalPaid,0) AS [Current Debt], Trim(C.Firstname &
" " & C.Lastname) AS [Debtor's Name],
IIf(DisputedSW=True,"Disputed","Undisputed") AS [Type of Debt],
IIf(P.PayAge30,'X','') AS [Age 30 Days], IIf(P.PayAge=30 And
P.PayAge=90,'X','') AS [Age 30-90 Days], IIf(P.PayAge90,'X','') AS [Age 90
Days], IIf(NZ(HomeAddressStreet,'')='','',HomeAddressStre et & ', ' &
HomeAddressCity & ', ' & HomeAddressState & ' ' & HomeAddressZip) AS
[Debtor's Address], C.PID AS [Debtor's Personal ID], C.DOB AS [Debtor's Date
of Birth], C.Phone AS [Other Debtor Contact Data], C.CaseNumber AS [Agency
Debt Identifier], C.Comments3 AS [Collection Efforts to Date]
FROM [Beth AFirms] AS C LEFT JOIN qryCasePayments AS P ON
C.CaseNumber=P.CaseNumber
WHERE C.Unit = 'Member Fraud' AND (C.Restitution-NZ(P.TotalPaid,0))0
ORDER BY C.Lastname;

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

It may be a corrupt Index on LastName. Try opening the table [Beth AFirms] in
design view and select the Lastname field. Does it have an index?

Or... shudder... did you make it a Lookup field?? That would sort by the ID,
not by the text name.
--

John W. Vinson [MVP]
.