My Sort Won't Sort

[mark.wolven]

What's up with this?

I have a query, and the first field is a prompt field to select a set
of records.

The second field is named [Fielding Slot] and is a text field. There
are 10 values:

01-C
02-1B
03-2B
04-3B
05-SS
06-MI
07-CI
08-OF
09-UT/P
10-P

The problem is that it doesn't sort in that order. The "03-2B" value
usually comes first.

What's the deal here?

 

[Rick Brandt]

What's up with this?

I have a query, and the first field is a prompt field to select a set
of records.

The second field is named [Fielding Slot] and is a text field. There
are 10 values:

01-C
02-1B
03-2B
04-3B
05-SS
06-MI
07-CI
08-OF
09-UT/P
10-P

The problem is that it doesn't sort in that order. The "03-2B" value
usually comes first.

What's the deal here?

You don't actually state in your post, but you DID tell the query to sort on
that field right?

 

[mark.wolven]

What's up with this?

I have a query, and the first field is a prompt field to select a set
of records.

The second field is named [Fielding Slot] and is a text field. There
are 10 values:

01-C
02-1B
03-2B
04-3B
05-SS
06-MI
07-CI
08-OF
09-UT/P
10-P

The problem is that it doesn't sort in that order. The "03-2B" value
usually comes first.

What's the deal here?

You don't actually state in your post, but you DID tell the query to sort on
that field right?

Yes, in the query, the field [fielding slot] is set to sort ascending.

 

[John W. Vinson]

What's up with this?

I have a query, and the first field is a prompt field to select a set
of records.

The second field is named [Fielding Slot] and is a text field. There
are 10 values:

01-C
02-1B
03-2B
04-3B
05-SS
06-MI
07-CI
08-OF
09-UT/P
10-P

The problem is that it doesn't sort in that order. The "03-2B" value
usually comes first.

What's the deal here?

Please post the SQL view of the query, and describe where you're seeing the
incorrect sort. Note that Reports do NOT honor their recordsource query's sort
order - you must specify the sort order in the report's Sorting and Grouping
dialog.

 

[mark.wolven]

What's up with this?

I have a query, and the first field is a prompt field to select a set
of records.

The second field is named [Fielding Slot] and is a text field. There
are 10 values:

01-C
02-1B
03-2B
04-3B
05-SS
06-MI
07-CI
08-OF
09-UT/P
10-P

The problem is that it doesn't sort in that order. The "03-2B" value
usually comes first.

What's the deal here?

Please post the SQL view of the query, and describe where you're seeing the
incorrect sort. Note that Reports do NOT honor their recordsource query's sort
order - you must specify the sort order in the report's Sorting and Grouping
dialog.

Absolutely:

SELECT DraftTracker.HCRLTeam, DraftTracker.[Fielding Slot],
DraftTracker.PlayerID, GBP_2007.[LastName,Firstname],
GBP_2007.Eligibility, GBP_2007.[CA ], GBP_2007.[1B ], GBP_2007.[2B ],
GBP_2007.[3B ], GBP_2007.[SS ], GBP_2007.of
FROM (DraftTracker INNER JOIN GBP_2007 ON DraftTracker.PlayerID =
GBP_2007.PlayerID) INNER JOIN HCRLTeams ON DraftTracker.HCRLTeam =
HCRLTeams.HCRL
WHERE (((DraftTracker.HCRLTeam)=[Team Abbr]))
ORDER BY DraftTracker.[Fielding Slot];


And the [fielding slot] data is returned in this order:
02-1B
03-2B
05-3B
01-C
01-C
07-MI
08-OF
08-OF
09-UT/P

 

[mark.wolven]

What's up with this?
I have a query, and the first field is a prompt field to select a set
of records.
The second field is named [Fielding Slot] and is a text field. There
are 10 values:
01-C
02-1B
03-2B
04-3B
05-SS
06-MI
07-CI
08-OF
09-UT/P
10-P
The problem is that it doesn't sort in that order. The "03-2B" value
usually comes first.
What's the deal here?

Please post the SQL view of the query, and describe where you're seeing the
incorrect sort. Note that Reports do NOT honor their recordsource query's sort
order - you must specify the sort order in the report's Sorting and Grouping
dialog.
--

John W. Vinson [MVP]

Absolutely:

SELECT DraftTracker.HCRLTeam, DraftTracker.[Fielding Slot],
DraftTracker.PlayerID, GBP_2007.[LastName,Firstname],
GBP_2007.Eligibility, GBP_2007.[CA ], GBP_2007.[1B ], GBP_2007.[2B ],
GBP_2007.[3B ], GBP_2007.[SS ], GBP_2007.of
FROM (DraftTracker INNER JOIN GBP_2007 ON DraftTracker.PlayerID =
GBP_2007.PlayerID) INNER JOIN HCRLTeams ON DraftTracker.HCRLTeam =
HCRLTeams.HCRL
WHERE (((DraftTracker.HCRLTeam)=[Team Abbr]))
ORDER BY DraftTracker.[Fielding Slot];

And the [fielding slot] data is returned in this order:
02-1B
03-2B
05-3B
01-C
01-C
07-MI
08-OF
08-OF
09-UT/P

OK, looking at this, it's sorting based on the fourth character:
1,2,3,C,M,O,U. Why would it be ignoring the 01, 02, 03 aspect of the
data?

 

[Rick Brandt]

SELECT DraftTracker.HCRLTeam, DraftTracker.[Fielding Slot],
DraftTracker.PlayerID, GBP_2007.[LastName,Firstname],
GBP_2007.Eligibility, GBP_2007.[CA ], GBP_2007.[1B ], GBP_2007.[2B ],
GBP_2007.[3B ], GBP_2007.[SS ], GBP_2007.of
FROM (DraftTracker INNER JOIN GBP_2007 ON DraftTracker.PlayerID =
GBP_2007.PlayerID) INNER JOIN HCRLTeams ON DraftTracker.HCRLTeam =
HCRLTeams.HCRL
WHERE (((DraftTracker.HCRLTeam)=[Team Abbr]))
ORDER BY DraftTracker.[Fielding Slot];


And the [fielding slot] data is returned in this order:
02-1B
03-2B
05-3B
01-C
01-C
07-MI
08-OF
08-OF
09-UT/P

If you ever applied a sort with the toolbar while the query was in datasheet
view that can get saved even though it does not actually alter the SQL of
the query. Try opening the advanced filter tool while in datasheet view to
see if that has a sort order in it. If it does then clear that filter/sort
out and save.

If this turns out to be your problem I don't think it would affect your
application's usage of the query even if you didn't fix it. I'm pretty sure
that filters and sorting applied in that manner only affect actually viewing
the datasheet. The data from the query as it would be passed to forms,
reports, or code would not be affected.

 

[KARL DEWEY]

I would check for leading spaces by changing the query like this ---
ORDER BY Trim(DraftTracker.[Fielding Slot]);

--
KARL DEWEY
Build a little - Test a little

What's up with this?

I have a query, and the first field is a prompt field to select a set
of records.

The second field is named [Fielding Slot] and is a text field. There
are 10 values:

01-C
02-1B
03-2B
04-3B
05-SS
06-MI
07-CI
08-OF
09-UT/P
10-P

The problem is that it doesn't sort in that order. The "03-2B" value
usually comes first.

What's the deal here?

Please post the SQL view of the query, and describe where you're seeing the
incorrect sort. Note that Reports do NOT honor their recordsource query's sort
order - you must specify the sort order in the report's Sorting and Grouping
dialog.

Absolutely:

SELECT DraftTracker.HCRLTeam, DraftTracker.[Fielding Slot],
DraftTracker.PlayerID, GBP_2007.[LastName,Firstname],
GBP_2007.Eligibility, GBP_2007.[CA ], GBP_2007.[1B ], GBP_2007.[2B ],
GBP_2007.[3B ], GBP_2007.[SS ], GBP_2007.of
FROM (DraftTracker INNER JOIN GBP_2007 ON DraftTracker.PlayerID =
GBP_2007.PlayerID) INNER JOIN HCRLTeams ON DraftTracker.HCRLTeam =
HCRLTeams.HCRL
WHERE (((DraftTracker.HCRLTeam)=[Team Abbr]))
ORDER BY DraftTracker.[Fielding Slot];


And the [fielding slot] data is returned in this order:
02-1B
03-2B
05-3B
01-C
01-C
07-MI
08-OF
08-OF
09-UT/P

 

[mark.wolven]

I would check for leading spaces by changing the query like this ---
ORDER BY Trim(DraftTracker.[Fielding Slot]);

--
KARL DEWEY
Build a little - Test a little

On Tue, 25 Mar 2008 18:44:01 -0700 (PDT), (e-mail address removed) wrote:
What's up with this?
I have a query, and the first field is a prompt field to select a set
of records.
The second field is named [Fielding Slot] and is a text field. There
are 10 values:
01-C
02-1B
03-2B
04-3B
05-SS
06-MI
07-CI
08-OF
09-UT/P
10-P
The problem is that it doesn't sort in that order. The "03-2B" value
usually comes first.
What's the deal here?
Please post the SQL view of the query, and describe where you're seeing the
incorrect sort. Note that Reports do NOT honor their recordsource query's sort
order - you must specify the sort order in the report's Sorting and Grouping
dialog.

Absolutely:

SELECT DraftTracker.HCRLTeam, DraftTracker.[Fielding Slot],
DraftTracker.PlayerID, GBP_2007.[LastName,Firstname],
GBP_2007.Eligibility, GBP_2007.[CA ], GBP_2007.[1B ], GBP_2007.[2B ],
GBP_2007.[3B ], GBP_2007.[SS ], GBP_2007.of
FROM (DraftTracker INNER JOIN GBP_2007 ON DraftTracker.PlayerID =
GBP_2007.PlayerID) INNER JOIN HCRLTeams ON DraftTracker.HCRLTeam =
HCRLTeams.HCRL
WHERE (((DraftTracker.HCRLTeam)=[Team Abbr]))
ORDER BY DraftTracker.[Fielding Slot];

And the [fielding slot] data is returned in this order:
02-1B
03-2B
05-3B
01-C
01-C
07-MI
08-OF
08-OF
09-UT/P

Thanks for all your tips. But I found the root cause, in the query, if
I select the field, and look at its properties, there's an "row
source" property, which was set to sort by a different field, on a
different table. The values that I listed are part of a table, and
those values were sorted in a different order.