Sorting anomaly

  • Thread starter Thread starter Kevin
  • Start date Start date
K

Kevin

I have a group of records which contain a customer text ID. Using an
Access sort which sorts on that ID ascending, I continually get records
in this order:

lak-b90
lak-b92
lak-c93
lakin
lak-m801
lak-m93
lak-n100

Can anyone tell me what lakin is doing in the middle there? I have
compacted and repaired, and even changed the name to lak (it goes to the
top) and then back to lakin (there it is again in the middle).

A thoroughly perplexed Kevin
 
Hi,
I entered your values into a table and then built a query, sorting on that field.
This is what I got:
lak-b90
lak-b92
lak-c93
lak-m801
lak-m93
lak-n100
lakin

Are you using a query to sort? If so, do you have any other fields in your Order By?
 
In looking at this, I probably could have simplified it.. it was a quick
and dirty copy from another query... but that still doesn't explain the
results.

SELECT tCustomers.CustKey, tCustomers.ID, [tcustomers].[CustName] & " ("
& [tcustomers].[City] & ")" AS CustName, qCustomersFull.Contact,
qCustomersFull.Phone, tCustomers.OurLocation,
qCustomersFull.CustFullAddress, tCustomers.Terms, tCustomers.Taxable,
tCustomers.TaxCode, tCustomers.ScaleCust, tCustomers.RoutingCust,
tCustomers.Prepay, tCustomers.RoundTotal
FROM tCustomers LEFT JOIN qCustomersFull ON tCustomers.CustKey =
qCustomersFull.tCustKey
ORDER BY tCustomers.ID;

___________________________________________

 
A friend suggested that Access is ignoring the dashes in doing the sort,
and that would indeed explain the results, but I find it hard to believe
this is what's happening.

Kevin

---------------------------------------------------


Show us the SQL

TC


 
Ahah! So my friend was right! "More or less ignored" is apparently
putting it mildly! (I guess Dan Artuso isn't using Jet 4 yet, and
thanks to Van for the quotes around 'correctly'.)

So how do we recover from my ever-increasing suspicion that if MS
programmers have a choice of 2 ways to do things, they will invariably
make the wrong choice? (At least in Access 2003, which I just converted
to, DAO is a default reference, and Allow Design Changes defaults to
Design View Only, but in reports AutoResize is still Yes and AutoCenter
is No. Who are these people listening to????) (Yes, I do know about
Normal, but it took me a long time to find it.)

Sorry, gentlemen, I am venting, but not at you.... You have my
everlasting appreciation. Thank you! But I'd love to hear the
rationale from the design staff for this. The fact that the SQL
designers made the same decision is no excuse. (Hey, it MIGHT be a
minus sign, so let's make EVERYBODY change their code, is my guess...!)

(Unfortunately I have the same opinion about the handling of nulls: if
..095% of apps need the distinction in .095% of their fields, let's make
EVERYBODY write code to handle the difference in EVERY line of code they
write! And by the way, let's REALLY screw them up on dates! Would you
rather have 12/31/1899 or 12:00:00 am if you don't have a date? Uuuh,
yeah, right!)

Apologies again for venting, and thanks to you! Guess I'll just have to
write some routines which change all the dashes my clients have used and
have gotten used to to underscores (Do THEY work??!!), and blame MS for
what I'll have to charge them.

Kevin (Ever the PITA)

-------------------------------------------------------------
In JET 4, hyphen is treated as a special character and
more or less ignored. If you strip the hyphen you have:

....
lakc93
lakin
lakm801
....

which are sorted "correctly".

See the MSKB article:

<http://support.microsoft.com/?id=236952>

HTH
Van T. Dinh
MVP (Access)
 
Huh?

Can you provide a reference, &/or, does everyone else agree with this?

Not disagreeing - just asking. I can't see any sense in making that change.

TIA,
TC
 
Back
Top