ORDER BY clause not working ?!?

  • Thread starter Thread starter Dorian
  • Start date Start date
D

Dorian

Can anyone explain why this query, when run, does not return data sorted in
the requested (order by clause) sequence?

SELECT R.ReferralSource, R.LastReferral AS ReferralDate, C.*
FROM qryCaseData AS C LEFT JOIN qryLatestReferral2 AS R ON C.CaseNumber =
R.CaseId
WHERE (C.Unit = [Unit: (Phoenix, Tucson)] OR NZ([Unit: (Phoenix,
Tucson)],'') = '') AND C.AssignedDate BETWEEN [Assigned date from
(mm/dd/yyyy)] AND [Assigned date to (mm/dd/yyyy)]
ORDER BY R.ReferralSource;
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
 
Can anyone explain why this query, when run, does not return data sorted in
the requested (order by clause) sequence?

SELECT R.ReferralSource, R.LastReferral AS ReferralDate, C.*
FROM qryCaseData AS C LEFT JOIN qryLatestReferral2 AS R ON C.CaseNumber =
R.CaseId
WHERE (C.Unit = [Unit: (Phoenix, Tucson)] OR NZ([Unit: (Phoenix,
Tucson)],'') = '') AND C.AssignedDate BETWEEN [Assigned date from
(mm/dd/yyyy)] AND [Assigned date to (mm/dd/yyyy)]
ORDER BY R.ReferralSource;
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
Click to expand...

How would any of us know?
You haven't told us the R.ReferralSource datatype, nor given us an
example of the data, nor an example of the way it is sorting, nor an
example of the way you think it should sort.
 
Do you have numbers in a text field like 1, 2, 3, ... 9, 10, 11, etc.? Are
you getting 1, 10, 11, 12, ... 2, 20, 21, ...3, 30, 31, ...?

You have ...OR NZ([Unit: (Phoenix, Tucson)],'') = '') AND ...
If [Unit: (Phoenix, Tucson)] is null what do you want to use instead of the
null?

Maybe it should be like this --
WHERE (C.Unit = [Unit: (Phoenix, Tucson)] OR [Unit: (Phoenix, Tucson)] Is
Null AND ...
 
I had always assumed that queries returned results sequenced by the ORDER BY
clause. In this case R.ReferralSource is a text field. The results are coming
back in random order as far as I can tell.
Something odd is happening because I get prompted for each parameter four
times.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


KARL DEWEY said:
Do you have numbers in a text field like 1, 2, 3, ... 9, 10, 11, etc.? Are
you getting 1, 10, 11, 12, ... 2, 20, 21, ...3, 30, 31, ...?

You have ...OR NZ([Unit: (Phoenix, Tucson)],'') = '') AND ...
If [Unit: (Phoenix, Tucson)] is null what do you want to use instead of the
null?

Maybe it should be like this --
WHERE (C.Unit = [Unit: (Phoenix, Tucson)] OR [Unit: (Phoenix, Tucson)] Is
Null AND ...

--
Build a little, test a little.


Dorian said:
Can anyone explain why this query, when run, does not return data sorted in
the requested (order by clause) sequence?

SELECT R.ReferralSource, R.LastReferral AS ReferralDate, C.*
FROM qryCaseData AS C LEFT JOIN qryLatestReferral2 AS R ON C.CaseNumber =
R.CaseId
WHERE (C.Unit = [Unit: (Phoenix, Tucson)] OR NZ([Unit: (Phoenix,
Tucson)],'') = '') AND C.AssignedDate BETWEEN [Assigned date from
(mm/dd/yyyy)] AND [Assigned date to (mm/dd/yyyy)]
ORDER BY R.ReferralSource;
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
Click to expand...
Click to expand...
 
You did not answer my question about having numbers in a text field.
And you have ...OR NZ([Unit: (Phoenix, Tucson)],'') = '') AND ...
If [Unit: (Phoenix, Tucson)] is null what do you want to use instead of the
null?
Post sample data in the order returned.
Which parameter are you prompted four times?

--
Build a little, test a little.


Dorian said:
I had always assumed that queries returned results sequenced by the ORDER BY
clause. In this case R.ReferralSource is a text field. The results are coming
back in random order as far as I can tell.
Something odd is happening because I get prompted for each parameter four
times.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


KARL DEWEY said:
Do you have numbers in a text field like 1, 2, 3, ... 9, 10, 11, etc.? Are
you getting 1, 10, 11, 12, ... 2, 20, 21, ...3, 30, 31, ...?

You have ...OR NZ([Unit: (Phoenix, Tucson)],'') = '') AND ...
If [Unit: (Phoenix, Tucson)] is null what do you want to use instead of the
null?

Maybe it should be like this --
WHERE (C.Unit = [Unit: (Phoenix, Tucson)] OR [Unit: (Phoenix, Tucson)] Is
Null AND ...

--
Build a little, test a little.


Dorian said:
Can anyone explain why this query, when run, does not return data sorted in
the requested (order by clause) sequence?

SELECT R.ReferralSource, R.LastReferral AS ReferralDate, C.*
FROM qryCaseData AS C LEFT JOIN qryLatestReferral2 AS R ON C.CaseNumber =
R.CaseId
WHERE (C.Unit = [Unit: (Phoenix, Tucson)] OR NZ([Unit: (Phoenix,
Tucson)],'') = '') AND C.AssignedDate BETWEEN [Assigned date from
(mm/dd/yyyy)] AND [Assigned date to (mm/dd/yyyy)]
ORDER BY R.ReferralSource;
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
Click to expand...
Click to expand...
Click to expand...
 
Back
Top