Re: Sort Multi Time Fields


Ken,

Looks good now. Thanks for all your help.

John


"Ken Snell [MVP]" wrote:

Are you doing this in query design view? If yes:

Fx_A_TIME_SORT: CDate(Nz([F2_A_TIME],[F1_A_TIME]))

--

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


"John B" <JohnB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:5C470149-CA3D-4BAF-97CF-894049E44782@xxxxxxxxxxxxxxxx
Ken,

I get an invalid syntax at AS.

John


"Ken Snell [MVP]" wrote:

OK, let's slightly modify the new sorting field:

CDate(Nz([F2_A_TIME],[F1_A_TIME])) AS Fx_A_TIME_SORT

--

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




"John B" <JohnB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E02BEE0A-7887-4DD5-A3F5-156C5B22D511@xxxxxxxxxxxxxxxx
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
    ... Ken Snell ... <MS ACCESS MVP> ... I do have the report sorting and grouping by ... Save the query. ...
    (microsoft.public.access.reports)
  • Re: Aging Receivables Not working
    ... run the report without any data in it. ... "Ken Snell " wrote: ... <MS ACCESS MVP> ... I'm managed to get the query to work without error messages, ...
    (microsoft.public.access.reports)
  • Re: Combo Box text display problem based on AfterUpdate...
    ... You need to make these changes so that the query will work. ... INNER JOIN tblManager AS M ... Ken Snell ... <MS ACCESS MVP> ...
    (microsoft.public.access.forms)
  • Re: Export by group to a specific folder and name
    ... With respect to exporting a report, ... except that the query would be the one on which the report is ... "Ken Snell MVP" wrote: ...
    (microsoft.public.access.externaldata)
  • Re: query not working
    ... not removing the control values thereby allowing a duplicate record to be ... "Ken Snell " wrote: ... that should cause your query to have a record. ... <MS ACCESS MVP> ...
    (microsoft.public.access.formscoding)