alphabetic sort problem

[steve]

I have a query that is supposed to be sorting alphabetically by location.
There are items that do not follow the sort sequence, please see below for
examples from the query. I would appreciate any advice on where to look to
correct this.
TIA

Steve

This is the way it looks after highlighting the column and hitting the A-Z
sort button (correct sort).



DELIVERY MORNING REPORT TABLE Query

DATE SCHEDULED
LOCATION


CEDAR BLUFF


CEDAR BLUFF


CLAYPOOL HILL


COUNCIL


GOLDEN AGE


HONAKER


HURLEY


JOLO


PAINTLICK


POUNDING MILL


RAVEN


RAVEN


WAR


WHITEWOOD




This is the way the report sorts with location selected to sort ascending
(wrong sort):



DELIVERY MORNING REPORT TABLE Query

DATE SCHEDULED
LOCATION


CEDAR BLUFF


CEDAR BLUFF


CLAYPOOL HILL


COUNCIL


HONAKER


HURLEY


JOLO


POUNDING MILL


PAINTLICK


RAVEN


RAVEN


WHITEWOOD


WAR


GOLDEN AGE

 

[steve]

I have checked the location table and there are no leading spaces anywhere.
That said, why would it sort correctly when using the A-Z button but not
when the query runs?

Steve

 

[Van T. Dinh]

I think the tiny hint of the cause is the word "report" in your sentence:

This is the way the report sorts with location selected to sort ascending
(wrong sort):
[unquote]

The Report processing ignores the sorting (ORDER BY clause) in your Query!
Records / rows are retrieved randomly (to us) as Access/JET sees fit so
records / rows retrieved can be in any order ...

If you want to sort in the Report, you need to use the "Sorting and
Grouping" feature of the Report (more flexible) or the [Order By] and [Order
By On] Properties of the Report (less flexible).

 

[Marshall Barton]

You should post a Copy/Paste of the query's SQL statement so
we can see what you're doing.

Is the location field (in the table) a lookup field? If it
is, you are sorting the location's ID number, not its name.

 

[Marshall Barton]

Good eye's Van, I never saw that. Just goes to show how a
single word can be the most important clue.
--
Marsh
MVP [MS Access]

I think the tiny hint of the cause is the word "report" in your sentence:

This is the way the report sorts with location selected to sort ascending
(wrong sort):
[unquote]

The Report processing ignores the sorting (ORDER BY clause) in your Query!
Records / rows are retrieved randomly (to us) as Access/JET sees fit so
records / rows retrieved can be in any order ...

If you want to sort in the Report, you need to use the "Sorting and
Grouping" feature of the Report (more flexible) or the [Order By] and [Order
By On] Properties of the Report (less flexible).

 

[steve]

Here is the SQL from the query used by the report. The location is a lookup
field so that is probably the problem. Is there a fix for it?

SELECT [DELIVERY MORNING REPORT TABLE].[DATE SCHEDULED], [DELIVERY MORNING
REPORT TABLE].LOCATION, [DELIVERY MORNING REPORT TABLE].[LAST NAME],
[DELIVERY MORNING REPORT TABLE].[FIRST NAME], [DELIVERY MORNING REPORT
TABLE].MI, [DELIVERY MORNING REPORT TABLE].[PATIENT PHONE], [DELIVERY
MORNING REPORT TABLE].[DATE REC], [DELIVERY MORNING REPORT TABLE].[TIME
REC], [DELIVERY MORNING REPORT TABLE].DELIVERY, [DELIVERY MORNING REPORT
TABLE].[DELIVERY METHOD], [DELIVERY MORNING REPORT TABLE].[TIME SCHEDULED],
[DELIVERY MORNING REPORT TABLE].[ASSIGNED TO], [DELIVERY MORNING REPORT
TABLE].[DATE COMPLETE], [DELIVERY MORNING REPORT TABLE].[IC NEEDED],
[DELIVERY MORNING REPORT TABLE].EQUIPMENT, [DELIVERY MORNING REPORT
TABLE].[EQUIPMENT 2], [DELIVERY MORNING REPORT TABLE].[EQUIPMENT 3],
[DELIVERY MORNING REPORT TABLE].[EQUIPMENT 4], [DELIVERY MORNING REPORT
TABLE].[ON ORDER], [DELIVERY MORNING REPORT TABLE].ORDER, [DELIVERY MORNING
REPORT TABLE].[PRE AUTH NEEDED], [DELIVERY MORNING REPORT TABLE].[F/U DATE],
[DELIVERY MORNING REPORT TABLE].[GR CHART], [DELIVERY MORNING REPORT
TABLE].[RECEIVED BY]
FROM [DELIVERY MORNING REPORT TABLE]
WHERE ((([DELIVERY MORNING REPORT TABLE].[DATE SCHEDULED]) Between [Enter
the Beginning Date] And [Enter the Ending Date] Or ([DELIVERY MORNING REPORT
TABLE].[DATE SCHEDULED]) Is Null))
ORDER BY [DELIVERY MORNING REPORT TABLE].LOCATION;

Thanks to all for the help.

Steve

Good eye's Van, I never saw that. Just goes to show how a
single word can be the most important clue.
--
Marsh
MVP [MS Access]

I think the tiny hint of the cause is the word "report" in your sentence:

This is the way the report sorts with location selected to sort ascending
(wrong sort):
[unquote]

The Report processing ignores the sorting (ORDER BY clause) in your Query!
Records / rows are retrieved randomly (to us) as Access/JET sees fit so
records / rows retrieved can be in any order ...

If you want to sort in the Report, you need to use the "Sorting and
Grouping" feature of the Report (more flexible) or the [Order By] and
[Order
By On] Properties of the Report (less flexible).

 

[steve]

One more thought, why does the sort work in the query grid using the A-Z
sort button?

Steve

Good eye's Van, I never saw that. Just goes to show how a
single word can be the most important clue.
--
Marsh
MVP [MS Access]

I think the tiny hint of the cause is the word "report" in your sentence:

This is the way the report sorts with location selected to sort ascending
(wrong sort):
[unquote]

The Report processing ignores the sorting (ORDER BY clause) in your Query!
Records / rows are retrieved randomly (to us) as Access/JET sees fit so
records / rows retrieved can be in any order ...

If you want to sort in the Report, you need to use the "Sorting and
Grouping" feature of the Report (more flexible) or the [Order By] and
[Order
By On] Properties of the Report (less flexible).

 

[Van T. Dinh]

The A-Z button sorts according to what appears on screen, i.e. the looked-up
Text, not what's underneath, i.e. not the look-up key. The ORDER BY clause
and sorting in Report work an the actual value you specify so if you specify
the key, it will sort by the key, not the look-up Text.

That's why we avoid using Look-up Fields in Table Design. See The Access
Web article:

http://www.mvps.org/access/lookupfields.htm

--
HTH
Van T. Dinh
MVP (Access)

One more thought, why does the sort work in the query grid using the A-Z
sort button?

Steve

Good eye's Van, I never saw that. Just goes to show how a
single word can be the most important clue.
--
Marsh
MVP [MS Access]

I think the tiny hint of the cause is the word "report" in your sentence:

This is the way the report sorts with location selected to sort ascending
(wrong sort):
[unquote]

The Report processing ignores the sorting (ORDER BY clause) in your
Query!
Records / rows are retrieved randomly (to us) as Access/JET sees fit so
records / rows retrieved can be in any order ...

If you want to sort in the Report, you need to use the "Sorting and
Grouping" feature of the Report (more flexible) or the [Order By] and
[Order
By On] Properties of the Report (less flexible).

 

[Van T. Dinh]

(Using the same terminology as per my other post)

Include you LookUp Table in the Query and sort by the LookUp Text not not
the LookUp key in your Report.

 

[steve]

Thank you Van, Karl and Dewey for all the help.

Steve

The A-Z button sorts according to what appears on screen, i.e. the
looked-up Text, not what's underneath, i.e. not the look-up key. The
ORDER BY clause and sorting in Report work an the actual value you specify
so if you specify the key, it will sort by the key, not the look-up Text.

That's why we avoid using Look-up Fields in Table Design. See The Access
Web article:

http://www.mvps.org/access/lookupfields.htm

--
HTH
Van T. Dinh
MVP (Access)

One more thought, why does the sort work in the query grid using the A-Z
sort button?

Steve

Good eye's Van, I never saw that. Just goes to show how a
single word can be the most important clue.
--
Marsh
MVP [MS Access]


Van T. Dinh wrote:

I think the tiny hint of the cause is the word "report" in your
sentence:

This is the way the report sorts with location selected to sort
ascending
(wrong sort):
[unquote]

The Report processing ignores the sorting (ORDER BY clause) in your
Query!
Records / rows are retrieved randomly (to us) as Access/JET sees fit so
records / rows retrieved can be in any order ...

If you want to sort in the Report, you need to use the "Sorting and
Grouping" feature of the Report (more flexible) or the [Order By] and
[Order
By On] Properties of the Report (less flexible).

 

[Gary Walter]

:

This is the way the report sorts with location selected to sort ascending
(wrong sort):

<snip>

PMFBI

Just to be clear in your method....

In report design, you clicked on

"Sorting and Grouping" icon

which brought up a dialog box with 2 columns

Field/Expression Sort Order

Location Ascending

Is this what you meant by "location selected..."?

I apologise if this disrespects your knowledge in any way,
but I have seen many posts where "words" mean one thing
to the poster, and mean another to helpers...

 

[Gary Walter]

Sorry...

when he did not
realize there were more posts in the thread:

 

[iliace]

Your "correct sort" still looks wrong. And it does look like there's a
leading space.

Use TRIM to be sure.