Sort order by customer name not working in query

[Mela]

Hello,

I have created a query based on a table that uses a lookup table for the
customer name. The query is showing all customers who have active contracts,
and it sorts some of the customers from A-Z, but after the Z-customer the
sort order is lost and the next customer begins with a "P''. So, it is
applying the sort order to some records, but not all. I've looked at the
troubleshooting entries in the Help menu, and also on this site, but cannot
find the problem (no leading spaces, etc.). In addition, I've created a
report based on the query, and it's not sorting in alpha order either despite
having set the sorting/grouping properties to sort by customer name in
ascending order. I'm going crazy - help!

Thank you.

Mary

 

[Mela]

Hello,

I have created a query based on a table that uses a lookup table for the
customer name. The query is showing all customers who have active contracts,
and it sorts some of the customers from A-Z, but after the Z-customer the
sort order is lost and the next customer begins with a "P''. So, it is
applying the sort order to some records, but not all. I've looked at the
troubleshooting entries in the Help menu, and also on this site, but cannot
find the problem (no leading spaces, etc.). In addition, I've created a
report based on the query, and it's not sorting in alpha order either despite
having set the sorting/grouping properties to sort by customer name in
ascending order. I'm going crazy - help!

Thank you.

Mary

 

[Jerry Whittle]

This is one of the reasons that many of us consider lookup fields in tables
pure evil.

If your query is based only one the one table then there is a good chance
that it's sorting on something else besides the name field. What you see is
not what you get with table lookup fields.

Your query needs both the table is has now and the customer table with the
appropriate join between the fields. Pull the name field(s) from the customer
table and sort on it.

Below is a great list of reasons to dump lookup fields.

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

Further check out the second commandment here:

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

 

[vanderghast]

I respectfully disagree with my collegue(s) here. Take as example, someone
who get problem with a division by zero, or with an explicit sum involving a
NULL (getting NULL as result): that is not a reason to say that zeros, or
null, are evils and must not be used.

No one is born with innate knowledge of zeros, nulls, ... and lookup. For
me, it is a matter of proper education, and if it 'hurts' the first time you
meet a problem with them, you get out stronger once you know how to solve
the problem, after all. And if you found 0, null, or lookup useful, they
still continue to be usefull, properly used.


Vanderghast, Access MVP

 

[Gina Whipp]

Mela,

I oticed you sais look-up table and not look-fields... You also said a
query with Customer Names. While I will agree with Jerry concerning look-up
fields in tables I am not 100% sure that is what you are using. So my
question would be if you in fact created a query with customer names... Is
your query If... FirstName &" "& LastName IS NULL then Company Name... that
combines it into one field CustomerName? (Or something along those lines)
If that is the case then please copy/paste your query because there might be
a NULL that is causing your alpha problems.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[John W. Vinson]

Hello,

I have created a query based on a table that uses a lookup table for the
customer name. The query is showing all customers who have active contracts,
and it sorts some of the customers from A-Z, but after the Z-customer the
sort order is lost and the next customer begins with a "P''. So, it is
applying the sort order to some records, but not all. I've looked at the
troubleshooting entries in the Help menu, and also on this site, but cannot
find the problem (no leading spaces, etc.). In addition, I've created a
report based on the query, and it's not sorting in alpha order either despite
having set the sorting/grouping properties to sort by customer name in
ascending order. I'm going crazy - help!

Thank you.

Mary

Please open your query in SQL view and post the SQL text here. Indicate which
fields (if any) are Lookup fields. As noted elsethread, it's probably sorting
by the concealed lookup field ID rather than by the actual text value of the
name field, but without seeing the query it's hard to say for sure.