Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Sort Order - What's After Z?

Posted on 1999-07-28
13
Medium Priority
?
393 Views
Last Modified: 2008-02-01
On the report for access 97, I am trying to sort a field in ascending order. One of the records in that fields is called ANY that I need to show at the very bottom of all other records for that field. When I apply sort, of course ANY pops up at the top with A's. I tried to use ASCII characters with ANY like *ANY or _ANY but they didn't seem to make any difference - in fact they made *ANY or _ANY appear at the top (not at the bottom after Z's) How can I make ANY appear after Z's at the bottom? Is there an ASCII character that I could tag on to the front of ANY that will allow me to do that?
0
Comment
Question by:alexy011899
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 25

Accepted Solution

by:
clockwatcher earned 280 total points
ID: 2002384
I'd add an additional field to the query your report is based on.  The new field's value is based on whether the field you wish to sort on is equal to "ANY".  Sort on the new field and then the original field.

If you're report isn't based on a query then change it to a query and add the additional field.

For example, say you're report is based on the table called myTable and that you're trying to sort on a field called myField.  Create a query:

select myTable.*, ucase([myField])<>"ANY" as firstsort from myTable

Use firstsort as your report's first sorting level and then myField as the next level.
0
 

Author Comment

by:alexy011899
ID: 2002385
my report is based on a query. could you explain a bit more please
0
 
LVL 25

Expert Comment

by:clockwatcher
ID: 2002386
Post your query and let me know the field you're trying to sort on.  I'll edit your current query and post the new query.
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:alexy011899
ID: 2002387
SELECT DISTINCTROW tblOrders.*, tblNetwork.NetworkName, IIf([Reports]![rptRepConf]![BillingRep]="CALENDAR/NOTARIZED",DateDiff("ww",[BegFlightDate],[EndFlightDate],1,3)+1,DateDiff("ww",[BegFlightDate],[EndFlightDate],2,3)+1) AS Wks, IIf([BillingMethod]="WEEKLY",IIf(IsNull([Dayscal]),[Rate]*[Freq]*[Wks],[Dayscal]*[Rate]*[Freq]*[Wks]),IIf(IsNull([Dayscal]),[Rate]*[Freq],[Dayscal]*[Rate]*[Freq])) AS Subtotal, IIf([Data]="/WK",IIf(IsNull([Dayscal]),[Freq]*[Wks],[Dayscal]*[Freq]*[Wks]),IIf(IsNull([Dayscal]),[Freq],[Dayscal]*[Freq])) AS SubFreq, tblNetwork.NetworkName FROM tblNetwork INNER JOIN tblOrders ON tblNetwork.NetworkID = tblOrders.Network WHERE (((tblOrders.OrderID)=[Reports]![rptRepConf]![OrderID])
0
 
LVL 25

Expert Comment

by:clockwatcher
ID: 2002388
Here's a little bit more explanation.  

Suppose you have the following table.

Table:  myTable

ID Color
1  Blue
2  Green
3  Blue
4  Any
5  Yellow
6  Any

I want to sort on Color but I want the Any's to show up on the bottom, so I create the following query.

select ucase([color])<>"ANY" as firstsort, color, id from myTable order by ucase([color])<>"ANY", color

The query will produce the following data sorted as follows:

firstsort, color, id

True, 1, Blue
True, 3, Blue  
True, 2, Green
True, 5, Yellow
False, 4, Any
False, 6, Any

You're sorting first by whether or not your value doesn't match "any", then you're sorting by color.
0
 
LVL 25

Expert Comment

by:clockwatcher
ID: 2002389
I need to know what field contains the "any"?  Which field is it that you're trying to sort by?
0
 

Author Comment

by:alexy011899
ID: 2002390
I am trying to sort by NetworkName
0
 
LVL 25

Expert Comment

by:clockwatcher
ID: 2002391
Just add the following item to the select list:

  ucase([tblNetwork]![NetworkName]) <> "ANY" as firstsort

so your query becomes:

SELECT DISTINCTROW ucase([tblNetwork]![NetworkName]) <> "ANY" as firstsort, tblOrders.*, tblNetwork.NetworkName,
... and the rest of the query.

And then change your report's sorting/grouping to sort by firstsort first and then by NetworkName.


You don't really need the Ucase function as Access compares are case insensitive, so you could really just use:

  [tblNetwork]![NetworkName] <> "ANY" as firstsort
0
 

Author Comment

by:alexy011899
ID: 2002392
IT WORKED !!!! Thank you. Have u had the same problem before or something?
0
 

Author Comment

by:alexy011899
ID: 2002393
Can i ask you one more question?
0
 

Author Comment

by:alexy011899
ID: 2002394
I am trying to bring the value of a control from subreport to report. The problem is that the control is in the subreport's report footer and is filled thru code.
0
 

Author Comment

by:alexy011899
ID: 2002395
When I try to bring its value to the detail section of the report it does not bring in the value. If I try to bring its value to the reports footer it works. I am using =SubreportName.Report!ControlName to bring the value from a subreport to the report. The question is how can I bring the value of a control from subreports footer to the report's detail section
0
 
LVL 25

Expert Comment

by:clockwatcher
ID: 2002396
I'd have do some testing on that one and I've got to head over to my girlfriend's right now.  

If you post it as a new question, someone else will probably be able to get to you sooner.  If it's still open tomorrow, I'll definitely devote some time to it.

Take care and goodluck,

Mark
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question