Re: Sort Multi Time Fields

Tech-Archive recommends: Speed Up your PC by fixing your registry


Ken,

We are slowly getting there. When it sorts now, it sorts numerically (ex:
1:51 PM, 10:47 PM, 11:25 AM, 11:46 AM, 2:30 PM, 4:41 PM, etc.). It is
ignoring the AM and PM. I do have the report sorting and grouping by date
(Field: F1_A_DATE) first. I probably should have mentioned this before, but I
believe this should not cause a problem. I have attached the new SQL query
statement with the Fx_A_TIME_SORT field.

SELECT DISTINCTROW Participants.STATUS, Participants.TRANS,
Transportation.NAMES, Transportation.TRAVELTOCOOP, Transportation.F1_D_DATE,
Transportation.F1_D_TIME, Transportation.F1_D_AIRPORT,
Transportation.F1_A_DATE, Transportation.F1_A_TIME,
Transportation.F1_A_AIRPORT, Transportation.F1_FLIGHT,
Transportation.F1_AIRLINE, Transportation.F1_NUMBER, Transportation.F1_NOTES,
Transportation.F2_D_DATE, Transportation.F2_D_TIME,
Transportation.F2_D_AIRPORT, Transportation.F2_A_DATE,
Transportation.F2_A_TIME, Transportation.F2_A_AIRPORT,
Transportation.F2_FLIGHT, Transportation.F2_AIRLINE,
Transportation.F2_NUMBER, Transportation.F2_NOTES, Transportation.F3_D_DATE,
Transportation.F3_D_TIME, Transportation.F3_D_AIRPORT,
Transportation.F3_A_DATE, Transportation.F3_A_TIME,
Transportation.F3_A_AIRPORT, Transportation.F3_FLIGHT,
Transportation.F3_AIRLINE, Transportation.F3_NUMBER, Transportation.F3_NOTES,
Nz([F2_A_TIME],[F1_A_TIME]) AS Fx_A_TIME_SORT
FROM Participants INNER JOIN Transportation ON Participants.PID =
Transportation.PID
WHERE (((Participants.STATUS)="A") AND ((Transportation.NAMES)>" ") AND
((Transportation.TRAVELTOCOOP)=Yes));

Hopefully this helps. Thanks again for all your help. I will be off Friday,
so I won't be able to get back to you until Monday.

John


"Ken Snell [MVP]" wrote:

Open the query in design view. Go to the first empty column at right side of
grid. Put this expression in the Field: box:

Fx_A_TIME_SORT: Nz(F2_A_TIME, F1_A_TIME)


Save the query. Now the Fx_A_TIME_SORT field will be available to your
report to use for sorting.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




"John B" <JohnB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7E214DE8-4A39-4BA2-BFC9-9DE487CEDACF@xxxxxxxxxxxxxxxx
Ken,

Thanks for the info. I understand what you are talking about, but I have
never added a calculated field in a query. I did try and add one, but I am
unsure exactly what I need to put in the field for the calculation. What
would be the expression be that I add to the query? Thanks.

John


"Ken Snell [MVP]" wrote:

Aha, ok I now understand better what you're wanting to do. The sorting on
the report is working correctly because the person with the two-leg
flight
has an earlier time for the F1_A_TIME field than the other person. So the
two-leg person sorts first.

So I suggest that you add a calculated field (Fx_A_TIME_SORT) to your
query,
which will replace F1_A_TIME with F2_A_TIME when F2_A_TIME is not NULL:

SELECT DISTINCTROW Participants.STATUS, Participants.TRANS,
Transportation.NAMES, Transportation.TRAVELTOCOOP,
Transportation.F1_D_DATE,
Transportation.F1_D_TIME, Transportation.F1_D_AIRPORT,
Transportation.F1_A_DATE, Transportation.F1_A_TIME,
Transportation.F1_A_AIRPORT, Transportation.F1_FLIGHT,
Transportation.F1_AIRLINE, Transportation.F1_NUMBER,
Transportation.F1_NOTES,
Transportation.F2_D_DATE, Transportation.F2_D_TIME,
Transportation.F2_D_AIRPORT, Transportation.F2_A_DATE,
Transportation.F2_A_TIME, Transportation.F2_A_AIRPORT,
Transportation.F2_FLIGHT, Transportation.F2_AIRLINE,
Transportation.F2_NUMBER, Transportation.F2_NOTES,
Transportation.F3_D_DATE,
Transportation.F3_D_TIME, Transportation.F3_D_AIRPORT,
Transportation.F3_A_DATE, Transportation.F3_A_TIME,
Transportation.F3_A_AIRPORT, Transportation.F3_FLIGHT,
Transportation.F3_AIRLINE, Transportation.F3_NUMBER,
Transportation.F3_NOTES,
Nz(F2_A_TIME, F1_A_TIME) AS Fx_A_TIME_SORT
FROM Participants INNER JOIN Transportation ON Participants.PID =
Transportation.PID
WHERE (((Participants.STATUS)="A") AND ((Transportation.NAMES)>" ") AND
((Transportation.TRAVELTOCOOP)=Yes));



Then use the Fx_A_TIME_SORT field as the sorting field in your report,
instead of F1_A_TIME_SORT and F2_A_TIME_SORT.


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



"John B" <JohnB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:173395A9-9159-4275-9991-AE45035948F5@xxxxxxxxxxxxxxxx
Ken,

All the F#_A_TIME are Date/Time Data Type fields. The information, for
the
report, is being pulled from a Query. I have pasted the SQL statement
for
the
query below:

SELECT DISTINCTROW Participants.STATUS, Participants.TRANS,
Transportation.NAMES, Transportation.TRAVELTOCOOP,
Transportation.F1_D_DATE,
Transportation.F1_D_TIME, Transportation.F1_D_AIRPORT,
Transportation.F1_A_DATE, Transportation.F1_A_TIME,
Transportation.F1_A_AIRPORT, Transportation.F1_FLIGHT,
Transportation.F1_AIRLINE, Transportation.F1_NUMBER,
Transportation.F1_NOTES,
Transportation.F2_D_DATE, Transportation.F2_D_TIME,
Transportation.F2_D_AIRPORT, Transportation.F2_A_DATE,
Transportation.F2_A_TIME, Transportation.F2_A_AIRPORT,
Transportation.F2_FLIGHT, Transportation.F2_AIRLINE,
Transportation.F2_NUMBER, Transportation.F2_NOTES,
Transportation.F3_D_DATE,
Transportation.F3_D_TIME, Transportation.F3_D_AIRPORT,
Transportation.F3_A_DATE, Transportation.F3_A_TIME,
Transportation.F3_A_AIRPORT, Transportation.F3_FLIGHT,
Transportation.F3_AIRLINE, Transportation.F3_NUMBER,
Transportation.F3_NOTES
FROM Participants INNER JOIN Transportation ON Participants.PID =
Transportation.PID
WHERE (((Participants.STATUS)="A") AND ((Transportation.NAMES)>" ") AND
((Transportation.TRAVELTOCOOP)=Yes));


Thanks.

John


"Ken Snell [MVP]" wrote:

The sorting that you're seeing indicates that the F1_A_TIME field is
being
seen as a text field, not a datetime field. What is the Datatype of
the
field in the table?
Are you using the table as the RecordSource for the report? If not,
post
the
SQL statement of the query that you're using for the report.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


"John B" <JohnB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:29349816-2D3A-46F9-9608-BBE996F88B73@xxxxxxxxxxxxxxxx
I have a report that I use for airline flights. I am looking for a
way
to
sort multiple time fields for connecting flights, by final arrival
time.
As
an example: Person A has a direct flight leaving at 9:05AM (Field:
F1_D_TIME)
and arriving at 11:45AM (Field: F1_A_TIME), Person B has a flight
leaving
at
8:25AM (Field: F1_D_TIME) and arriving at the connecting airport at
10:25AM
(Field: F1_A_TIME), then leaving the connecting airport for the last
leg
at
11:45AM (Field: F2_D_TIME) and arriving at 1:55PM (Field:
F2_A_TIME).
When
using Sorting and Grouping in the report, I have F1_A_TIME sorting
first
and
F2_A_TIME sorting second. What happens is Person B (1:55PM) comes
first
and
Person A (11:45AM) comes second. What I need is to have Person A
(11:45AM)
first, then Person B (1:55PM) second. This is only a small example,
as
I
have
multiple people with flights in the database. Any help on this would
be
greatly appreciated. Thanks.

John









.

Relevant Pages

  • Re: Sort Multi Time Fields
    ... <MS ACCESS MVP> ... When it sorts now, it sorts numerically (ex: ... I do have the report sorting and grouping by date ... Save the query. ...
    (microsoft.public.access.reports)
  • RE: Do Access Reports recalculate their record source?
    ... Reports ignore any sorting in a query, so a make table will not improve the ... Use the report Sorting and Grouping to order the report. ... Function JOB_INVENTORY(JOB As String, REF As Long, QTY As Long, CAP As Long) ...
    (microsoft.public.access.reports)
  • RE: Asking for old field
    ... Moreover, the query when run on its own, doesn't prompt for that ... > Then the report is hiding the previous name of the field somewhere. ... >>> Hi, neeraj. ... >>> Open the report in Design View and then open the Sorting and Grouping dialog ...
    (microsoft.public.access.security)
  • RE: Sorting in Query and Report involving look up combo box
    ... query and the report and the best way to do that is having the look up field. ... I have the report grouped by Artist Name and then Sorted by Style Number ... how do you have your sorting and grouping set up in your report. ... And, as I said before, Reports ignore the sort order of a query. ...
    (microsoft.public.access.reports)
  • RE: Sorting in Query and Report involving look up combo box
    ... Post the SQL of your query, ... how do you have your sorting and grouping set up in your report. ... And, as I said before, Reports ignore the sort order of a query. ...
    (microsoft.public.access.reports)